How can I order objects according to some attribute of the child in sqlalchemy?

Question

Here is the situation: I have a parent model say BlogPost. It has many Comments. What I want is the list of BlogPosts ordered by the creation date of its' Comments. I.e. the blog post which has the most newest comment should be on top of the list. Is this possible with SQLAlchemy?

Answer 1

http://www.sqlalchemy.org/docs/05/mappers.html#controlling-ordering

As of version 0.5, the ORM does not generate ordering for any query unless explicitly configured.

The “default” ordering for a collection, which applies to list-based collections, can be configured using the order_by keyword argument on relation():

Answer 2

I had the same question as the parent when using the ORM, and GHZ's link contained the answer on how it's possible. In sqlalchemy, assuming BlogPost.comments is a mapped relation to the Comments table, you can't do:

session.query(BlogPost).order_by(BlogPost.comments.creationDate.desc())

, but you can do:

session.query(BlogPost).join(Comments).order_by(Comments.creationDate.desc())