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?
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)