Getting the last related object for a set of objects in Peewee

Question

Suppose I have an object model A with a one-to-many relationship with B in Peewee using the sqlite backend. I want to get some set from A and join them with their last B. Is this a way to do this without a loop?

class A(Model): some_field = CharField() class B(Model): a = ForeignKeyField(A) date = DateTimeField(default=datetime.datetime.now) 

A naive way would be to call order_by and limit (1), but this applies to the whole request, so

 q = A.select().join(B).order_by(B.date.desc()).limit(1) 

naturally produce a singleton result like

 q = B.select().order_by(B.date.desc()).limit(1).join(A) 

I either use prefetch incorrectly or it doesn’t work for this because

 q1 = A.select() q2 = B.select().order_by(B.date.desc()).limit(1) q3 = prefetch(q1,q2) len(q3[0].a_set) len(q3[0].a_set_prefetch) 

None of these sets have a length of 1, if required. Does anyone know how to do this?

Answer 1

I understand that I need to understand the functions and group_by.

 q = B.select().join(A).group_by(A).having(fn.Max(B.date)==B.date) 

Answer 2

You can use it this way only if you need the latest date, not the last date record. If the last date entry is not the default ( datetime.datetime.now ), this request will be incorrect.

You can find the latest date entry:

 last_entry_date = B.select(B.date).order_by(B.id.desc()).limit(1).scalar() 

and related records A with this date:

with fields A and B:

 q = A.select(A, B).join(B).where(B.date == last_entry_date) 

with fields A only:

 q = B.select().join(A).where(B.date == last_entry_date) 

If you want to find the latest version of B.date (as for fn.Max(B.date) ) and use it as a where filter:

 latest_date = B.select(B.date).order_by(B.date.desc()).limit(1).scalar()