CODEWITHSUNDEEP
pythonsqlalchemy
anibal
anibal

Reputation: 439

SqlAlchemy select with max, group_by and order_by

I have to list the last modified resources for each group, for that I can do this query:

model.Session.query(
    model.Resource, func.max(model.Resource.last_modified)
).group_by(model.Resource.resource_group_id).order_by(
    model.Resource.last_modified.desc())

But SqlAlchemy complains with:

ProgrammingError: (ProgrammingError) column "resource.id" must appear in
the GROUP BY clause or be used in an aggregate function

How I can select only resource_group_id and last_modified columns?

In SQL what I want is this:

SELECT resource_group_id, max(last_modified) AS max_1
FROM resource GROUP BY resource_group_id ORDER BY max_1 DESC

Upvotes: 18

Views: 21408

Answers (2)

gonz
gonz

Reputation: 5276

You already got it, but I'll try to explain what's going on with the original query for future reference.

In sqlalchemy if you specified query(model.Resource, ...), a model reference, it will list each column on the resource table in the generated SQL select statement, so your original query would look something like:

SELECT resource.resource_group_id AS resource_group_id,
       resource.extra_column1 AS extra_column1,
       resource.extra_column2 AS extra_column2,
       ...
       count(resource.resource_group_id) AS max_1
GROUP BY resource_group_id ORDER BY max_1 DESC;

This won't work with a GROUP BY.

A common way to avoid this is to specify what columns you want to select explicitly by adding them to the query method .query(model.Resource.resource_group_id)

Upvotes: 5

anibal
anibal

Reputation: 439

model.Session.query(
    model.Resource.resource_group_id, func.max(model.Resource.last_modified)
).group_by(model.Resource.resource_group_id).order_by(
    func.max(model.Resource.last_modified).desc())

Upvotes: 12

Related Questions