Distinct within group by query

[Andrew M]

Hi,

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. 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)?

Thank you,

Andrew

[Varun Madiath]

Just so I understand what you're asking?

You want the minimum price per product (across all colors), as well as the list of colors?

A result set looking something like this?

| Attribute | Min Price | Colors           |
|-----------|-----------|------------------|
| Attr_1    | 10        | [blue,red,green] |
| Attr_2    | 200       | [violet,scarlet] |
| Attr_3    | 30        | [orange, yellow] |

Or do you want the min price per color, attribute combination?

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/949655c4-eacb-46fd-ae68-48a7aa1a3446%40googlegroups.com.

[Andrew M]

Thank you Varun - what you've shown in the table is exactly what I'm looking for.

[Varun Madiath]

What database are you using?

Some databases might support this natively, others would have to be emulated by the ORM.

I’m not a big user of the ORM, so someone else would have to help you with that.

If you’re using Postgres, then the array_agg function on the color column will give you exactly what you want.

I imagine that without an array_agg like function, two queries would have to be executed.

One to get the minimum price, the other to get a list of the colors using the logic that sqlalchemy ORM has to group rows into a list.

Then the results of these two queries would have to be presented as your result.

I’m curious to know if that’s possible with the ORM.

On Thu, Sep 5, 2019 at 12:10 AM Andrew M <and...@mackiefamily.org> wrote:

Thank you Varun - what you've shown in the table is exactly what I'm looking for.

--

SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/5426ccc8-a2ed-4d35-97ec-f7f9d53e29aa%40googlegroups.com.

[Andrew M]

Yes, I'm using Postgres.

This does exactly what I need:

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

Thanks Varun, that's saved me a real headache. I appreciate your help.