SQLAlchemy sort relationship by table in another relationship

Question

I write this question assuming that I'm doing something wrong instead of this behavior being a bug. In short, I have three tables, A, B, and C. A is in a one-to-many relationship with B and B is in a many-to-many relationship with C. I want to be able to set the relationship between C and B so that when I request the collection of B's associated with C, they come sorted by default based on a date column in A.

Here's a pared down version of actual code I'm trying. Note that I'm using Flask so you'll see some Flask_SQLAlchemy-isms in here:

from sqlalchemy import Column, Integer, ForeignKey, DateTime
from sqlalchemy.orm import relationship

from app import db
from sqlalchemy.sql.schema import Table


class A(db.Model):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    date = Column(DateTime, nullable=False)

    bs = relationship('B',
                      back_populates='a',
                      cascade='all, delete, delete-orphan')


class B(db.Model):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)

    a_id = Column(Integer, ForeignKey('a.id'))
    a = relationship('A',
                     back_populates='bs',
                     lazy='joined',
                     innerjoin=True)

    cs = relationship('C',
                      secondary='b_to_c',
                      back_populates='bs')


class C(db.Model):
    __tablename__ = 'c'

    id = Column(Integer, primary_key=True)
    timeStamp = Column(DateTime, index=True, nullable=False)

    bs = relationship('B',
                      secondary='b_to_c',
                      order_by='A.date',
                      back_populates='cs')


b_to_c = Table(
    'b_to_c',
    db.Model.metadata,
    Column('b_id', Integer, ForeignKey('b.id')),
    Column('c_id', Integer, ForeignKey('c.id'))
    )

I think you can see what I'm trying to achieve, but the code ends in an error. For example, when elsewhere in code I do something like

c = C.query.first()
if someObj not in c.bs:
    ...

It winds up throwing an exception on the second line, something like:

sqlalchemy.exc.OperationalError:
    (_mysql_exceptions.OperationalError)
    (1054, "Unknown column 'a.date' in 'order clause'")

[SQL: 'SELECT b.id AS b_id,
              b.a_id AS b_a_id,
              a_1.id AS a_1_id,
              a_1.date AS a_1_date,
    FROM b_to_c, b INNER JOIN a AS a_1 ON a_1.id = b.a_id
    WHERE %s = b_to_c.c_id AND b.id = b_to_c.b_id ORDER BY a.date']
[parameters: (2,)]

I noticed that, in the SQL query, a table references all seem to be subscripted with a _1 and if I change the ORDER BY to match and execute the SQL statement manually, it works fine. I have no idea what the purpose of that subscripting is but I don't know how to set up the table definitions such that it either doesn't happen or the ORDER BY is subscripted to match.

So, how do I define the tables / relationships better so I don't run into this issue?

SQLAlchemy sort relationship by table in another relationship

0 Answers