Fetching most recent related object for set of objects in Peewee

Question

Suppose I have an object model A with a one-to-many relationship with B in Peewee using an sqlite backend. I want to fetch some set of A and join each with their most recent B. Is their a way to do this without looping?

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

The naive way would be to call order_by and limit(1), but that would apply to the entire query, so

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

will naturally produce a singleton result, as will

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

I am either using prefetch wrong 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)

Neither of those sets has length 1, as desired. Does anyone know how to do this?

Answer 1

I realize I needed to understand 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 want the latest date and not the last entry of the date. If the last date entry isn't the default one (datetime.datetime.now) this query will be wrong.

You can find the last date entry:

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

and the related A records with this date:

with A and B fields:

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

with only the A fields:

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

If you want to find the latest B.date (as you do with the fn.Max(B.date)) and use it as the where filter:

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