How to order relationship objects on query execution

Question

The following code is for Flask-SQLAlchemy, but would be quite similar in SQLAlchemy.

I have two simple classes:

class Thread(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    subject = db.Column(db.String)
    messages = db.relationship('Message', backref='thread', lazy='dynamic')

class Message(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    created = db.Column(db.DateTime, default=datetime.utcnow())
    text = db.Column(db.String, nullable=False)

I would like to query all Threads and have them ordered by last message created. This is simple:

threads = Thread.query.join(Message).order_by(Message.created.desc()).all()

Threads is now a correctly ordered list I can iterate. However if I iterate over threads[0].messages then Messages objects are not ordered by Message.created descending.

I can solve this issue while declaring the relationship:

messages = relationship('Message', backref='thread', lazy='dynamic',
                                   order_by='Message.created.desc()')

However this is something I'd rather not do. I want explicitly set this while declaring my query.

I could also call:

threads[0].messages.reverse()

..but this is quite inconvenient in Jinja template.

Is there a good solution for setting order_by for joined model?

davidism · Accepted Answer · 2014-04-28 16:04:45Z

1

You have Thread.messages marked as lazy='dynamic'. This means that after querying for threads, messages is a query object, not a list yet. So iterate over threads[0].messages.order_by(Message.created.desc()).

answered Apr 28, 2014 at 16:04

davidism

128k31 gold badges417 silver badges348 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to order relationship objects on query execution

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related