peewee ORM - filter on a join using attributes from all models involved in the join

Question

@coleifer, Thank you for giving us Peewee.

I have a join from two Models that are connected by an FK constraint. I am querying the combined result with a query like the one below:

Model1.select(Model1, Model2).join(Model2).dicts()

I want to apply filters on the combined query, using attributes from both Models.

I was guided towards filter from the answer in this question: filter with dynamic dict peewee ORM

I can apply filter on Model1 attributes by appending filter at the end of join, like this: Model1.select(Model1, Model2).join(Model2).filter(Model1.name == 'foo').dicts() and that works as expected. I followed the documentation here: http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.filter

If I attempt to apply any filter with attributes from Model2 like filter(Model2.id == 22), it fails with an AttributeError.

I tried to create a CTE and applied filter on it but didn't work throwing an AttributeError.

is it possible to achieve what I am attempting? Please can you guide?

Answer 1

You should use .where() and not .filter():

query = (Model1
         .select(...)
         .join(Model2)
         .where((Model1.field == 1) & (Model2.other == 2)))

The docs provide many examples, please consult them. http://docs.peewee-orm.com/en/latest/peewee/querying.html#filtering-records