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?

van · Accepted Answer · 2011-06-06 11:48:17Z

23

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.

answered Jun 6, 2011 at 11:48

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

sqlalchemy filter by count column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related