Flask SQLAlchemy order_by relationship

Question

I'm working with a Flask application where I have a LargeGroupAttendance model that references another model called Attendee. I'm trying to request all of the LargeGroupAttendance objects that match a certain criteria, but I'm trying to sort them by a column of the Attendee model - is that even possible? Here are the two models below:

""" Attendeee Class """
class Attendee(Base):
    __tablename__ = 'attendee'

    id = Column(Integer, primary_key=True)
    first_name = Column(String(200))
    last_name = Column(String(200))
    year = Column(String(200))
    email = Column(String(100), unique=True)
    dorm = Column(String(100))

    def __init__(self, first_name, last_name, year, email, dorm):
        self.first_name = first_name
        self.last_name = last_name
        self.year = year
        self.email = email
        self.dorm = dorm

    def __repr__(self):
        return '<Attendee %r>' % self.first_name

""" Large Group Attendance Class """
class LargeGroupAttendance(Base):
    __tablename__ = 'large_group_attendance'

    id = Column(Integer, primary_key=True)
    first_time = Column(Integer)

    large_group_id = Column(Integer, ForeignKey('large_group.id'))
    large_group = relationship("LargeGroup", backref=backref('large_group_attendance', order_by=id))

    attendee_id = Column(Integer, ForeignKey('attendee.id'))
    attendee = relationship("Attendee", backref=backref('large_group_attendance', order_by=id))

Do I need to add something to my attendee class to make this possible? And here's a query I've tried before, but it's had no output (no errors either..). Where am I going wrong?

    attendance_records = db.session.query(LargeGroupAttendance).filter_by(large_group_id=event_id).order_by(desc(LargeGroupAttendance.attendee.first_name))

possible duplicate of [SQLAlchemy: How to order query results (order\_by) on a relationship's field?](http://stackoverflow.com/questions/9861990/sqlalchemy-how-to-order-query-results-order-by-on-a-relationships-field) — plaes, Jun 09 '15 at 05:00

score 19 · Accepted Answer · edited May 23 '17 at 12:08

I think you need add a join to your query, something like this:

.join(LargeGroupAttendance.attendee)

so that the final query would look like this:

attendance_records = (db.session.query(LargeGroupAttendance).
    filter_by(large_group_id = event_id).
    join(Attendee, LargeGroupAttendance.attendee).
    order_by(desc(Attendee.first_name))
    )

See SQLAlchemy: How to order query results (order_by) on a relationship's field? for a more detailed explanation

dvaerum · Answer 2 · 2023-01-23T00:36:53.617

3

I know this is an old post, but it showed up when I was searching, so maybe this will be useful to someone else

""" Attendeee Class """
class Attendee(Base):
    __tablename__ = 'attendee'

    id = Column(Integer, primary_key=True)
    first_name = Column(String(200))
    last_name = Column(String(200))
    year = Column(String(200))
    email = Column(String(100), unique=True)
    dorm = Column(String(100))

    ...

""" Large Group Attendance Class """
class LargeGroupAttendance(Base):
    __tablename__ = 'large_group_attendance'

    ...

    attendee_id = Column(Integer, ForeignKey('attendee.id'))
    attendee = relationship(
        "Attendee",
        backref=backref(
            'large_group_attendance',
            order_by=Attendee.__table__.columns.id
        )
    )

edited Jan 23 '23 at 00:36

answered Aug 21 '21 at 19:33

dvaerum

97
1
3

what if you need to order by multiple columns? – Pynchia Jul 19 '22 at 09:40
1

If my understanding of the code is correct, you can order_by multiple columns by using a list `order_by=[Attendee.__table__.columns.last_name, Attendee.__table__.columns.first_name]` this may however be wrong, it has been some time since I last dived deep into this – dvaerum Jul 20 '22 at 07:03

score 0 · Answer 3 · answered Apr 26 '23 at 15:19

I tried all other solutions not worked, only worked when used order_by in column relationship not in backref (uselist will return the query to confirm the order added in query)

sections = db.relationship('ConsultantFormSections', backref=backref('report', uselist=False),order_by='ConsultantFormSections.index.desc()', cascade="all, delete", lazy='dynamic', passive_deletes=True)

or

sections = db.relationship('ConsultantFormSections', backref=backref('report', uselist=False),order_by='consultant_form_sections.columns.index.desc()', cascade="all, delete", lazy='dynamic', passive_deletes=True)

Flask SQLAlchemy order_by relationship

3 Answers3