SQLAlchemy with count, group_by and order_by using the ORM

Question

I've got several function where I need to do a one-to-many join, using count(), group_by, and order_by. I'm using the sqlalchemy.select function to produce a query that will return me a set of id's, which I then iterate over to do an ORM select on the individual records. What I'm wondering is if there is a way to do what I need using the ORM in a single query so that I can avoid having to do the iteration.

Here's an example of what I'm doing now. In this case the entities are Location and Guide, mapped one-to-many. I'm trying get a list of the top locations sorted by how many guides they are related to.

def popular_world_cities(self):
    query = select([locations.c.id, func.count(Guide.location_id).label('count')],
                   from_obj=[locations, guides],
                   whereclause="guides.location_id = locations.id AND (locations.type = 'city' OR locations.type = 'custom')",
                   group_by=[Location.id],
                   order_by='count desc',
                   limit=10)
    return map(lambda x: meta.Session.query(Location).filter_by(id=x[0]).first(), meta.engine.execute(query).fetchall())

Solution

I've found the best way to do this. Simply supply a from_statement instead of a filter_by or some such. Like so:

meta.Session.query(Location).from_statement(query).all()

score 1 · Answer 1 · answered Dec 16 '08 at 07:30

What you're trying to do maps directly to a SQLAlchemy join between a subquery [made from your current select call] and a table. You'll want to move the ordering out of the subselect and create a separate, labeled column with count(desc); order the outer select by that column.

Other than that, I don't see much that's nonobvious.

score 1 · Answer 2 · edited Sep 13 '18 at 08:11

1

I found this the hard way but SQLAlchemy does support group_by. The documentation under Query doesn't say so but it does support it - I have used it!

And, you can also use order_by. I was going to create a special class/query like you did but then I found out there is a group_by() function.

edited Sep 13 '18 at 08:11

Smi

13,850
9
56
64

answered Mar 24 '09 at 22:17

4

And example might be useful here – user890739 Oct 23 '15 at 18:25
4

For example (taken from [here](http://stackoverflow.com/a/4086229/890739)) `session.query(Table.column, func.count(Table.column)).group_by(Table.column).all()` – user890739 Oct 23 '15 at 19:00

score 0 · Accepted Answer · answered Jan 22 '09 at 12:03

0

I've found the best way to do this. Simply supply a from_statement instead of a filter_by or some such. Like so:

meta.Session.query(Location).from_statement(query).all()

answered Jan 22 '09 at 12:03

Joshua Kifer

1,717
3
12
14

SQLAlchemy with count, group_by and order_by using the ORM

3 Answers3

Linked