sqlalchemy filter by count column

Question

I have a User query which filters by the amount of orders each user has (ordersCount).

User.query.filter('ordersCount>2')

If I run it it says: "Unknown column 'ordersCount' in 'where clause'"

From my experience I should be using having on such operations because mysql won't allow it for fields not part of the table but if I run it with having instead of filter I get:

(1054, "Unknown column 'ordersCount' in 'having clause'") 'SELECT count(1) AS count_1 \nFROM user \nHAVING ordersCount > 2' ()

So how do I filter a count column in sqlalchemy?

Answer 1

See documentation of Ordering, Grouping, Limiting, Offset...ing. Based on the sample code provided and an assumption that your orders are stored in the orders table, your version would look similar to:

>>> s = select([orders.c.user_id, func.count(orders.c.id)]).\
...     group_by(orders.c.user_id).having(func.count(orders.c.id) > 2)

In this way you will get the user_id's of the users in question. If you want to retrieve User objects, just make the s about a sub-query and join your User query with this sub-query to retrieve the uses in question.