CODEWITHSUNDEEP
pythonpostgresqlgroup-bysqlalchemydistinct
Andrew Mackie
Andrew Mackie

Reputation: 374

SQLAlchemy: Distinct inside group by

I would like to use distinct inside a group_by query, e.g.:

session.query(Product.attribute_x, func.min(Product.price), distinct(Product.color)). group_by(Product.attribute_x)

That is, for each value of attribute_x, I want to find the lowest price and all of the colors available.

I don't seem to be able to use distinct in this way in SQLAlchemy. Can anyone please suggest an efficient method of identifying the distinct colors for each value of attribute_x (without creating a new query for each value of attribute_x)?

I'm using SQLALchemy 1.3.4 on PostgreSQL.

Upvotes: 2

Views: 2086

Answers (1)

Ilja Everilä
Ilja Everilä

Reputation: 52939

Use array_agg:

from sqlalchemy.dialects.postgresql import array_agg

session.query(Product.attribute_x,
              func.min(Product.price),
              array_agg(distinct(Product.color))).\
    group_by(Product.attribute_x)

In this case func.array_agg() would work as well. The Postgresql dialect specific form just ensures that the return type is understood as a Postgresql ARRAY instead of the generic one, in case you want to use any of the array operators etc.

Upvotes: 3

Related Questions