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()

Answer 1

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.

Answer 2

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()

Answer 3

Guys, I found this the hard way but SQL Alchemy 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.

Hope this will help someone!!