pony.orm sort by newest Entity in relationship

Question

Let's say i have these tables mapped with pony.orm:

class Category(db.Entity):
    threads = Set("Thread")

class Thread(db.Entity):
    category = Required("Category")
    posts = Set("Post")

class Post(db.Entity):
    thread = Required("Thread")
    timestamp = Required(datetime)

Now I want to get all threads of a certain category ordered by there newest post:

With this line I get the ID of the newest post, but I want the object.

query = select((max(p.id), p.thread) for p in Post if p.thread.category.id == SOME_ID)
    .order_by(lambda post_id, thread: -post_id)

Of course I could [(Post[i], thread) for i, thread in query] or select(p for p in Post if p.id in [i for i,_ in query])

But this creates additional sql statements. So my question is: How can I get the newest posts of all threads in a certain category sorted by the timestamp of that post with a single sql statement.

I wouldn't using db.execute(sql) if you can't use the ORM.

Answer 1

Try this:

select((p, t) for t in Thread for p in t.posts
              if p.id == max(p2.id for p2 in t.posts)
       ).order_by(lambda p, t: desc(p.id))