CODEWITHSUNDEEP
pythonsqlsqlalchemy
ascobol
ascobol

Reputation: 7706

SQLAlchemy: filter on operator in a many-to-many relationship

I have two classes with a many-to-many relationship, Items and Categories.

Categories have an associated value.

I would like to query for all Items for which the highest Categorie.value (if there is any) is less than a given value.

So far I have tried queries like this:

from sqlalchemy.sql import functions 
Session.query(Items).join(Categories,Items.categories).filter(functions.max(Categories.value)<3.14).all()

But in this case I get a (OperationalError) misuse of aggregate function max() error.

Is there a way to make this query?

Upvotes: 1

Views: 619

Answers (1)

xli
xli

Reputation: 1308

You need GROUP BY and HAVING instead of just WHERE for filtering on an aggregate.

Session.query(Items).join(Items.categories).group_by(Items.id).having(functions.max(Categories.value)<3.14).all()

Edit: To also include Items without any category, I believe you can do an outer join and put an OR in the HAVING clause:

Session.query(Items).outerjoin(Items.categories).group_by(Items.id)\
.having( (functions.max(Categories.value)<3.14) | (functions.count(Categories.id)==0) )\
.all()

Upvotes: 2

Related Questions