SQLAlchemy with count, group_by and order_by using ORM

Question

I have several functions where I need to make a one-to-many connection using count (), group_by and order_by. I use the sqlalchemy.select function to create a query that will return a set of identifiers to me, which I then iterate over to select ORM on individual records. I am wondering if there is a way to do what I need using ORM in a single query so that I can avoid iterating.

Here is an example of what I'm doing now. In this case, the entities are Location and Guide, displayed one-to-many. I am trying to get a list of top places sorted by the number of guides with which they are associated.

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

Decision

I have found a better way to do this. Just put from_statement instead of filter_by or some of those. For example:

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

Answer 1

I have found a better way to do this. Just put from_statement instead of filter_by or some of those. For example:

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

Answer 2

What are you trying to do, maps directly in SQLAlchemy are connected between the subquery [made from your current select call] and the table. You will want to move the ordering from the subheading and create a separate column with a label indicating count (desc); order an external selection on this column.

Other than this, I do not see much of this unobvious.

Answer 3

Guys, I found this the hard way, but Alchemy SQL supports group_by. The documentation in the Request section does not talk about this, but supports it - I used it!

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

Hope this helps someone!