Re: [sqlalchemy] Distinct within group by query

[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  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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/5426ccc8-a2ed-4d35-97ec-f7f9d53e29aa%40googlegroups.com
> 
> .
>

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CADm-oGnXUagAU0L1G2Z7s_w8bJaELtM0if98_crvkvqcDrgiuQ%40mail.gmail.com.

Re: [sqlalchemy] Distinct within group by query

[Andrew M]

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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/5426ccc8-a2ed-4d35-97ec-f7f9d53e29aa%40googlegroups.com.

Re: [sqlalchemy] Distinct within group by query

[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?


On Wed, Sep 4, 2019 at 10:00 PM Andrew M  wrote:

> 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
>
> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/949655c4-eacb-46fd-ae68-48a7aa1a3446%40googlegroups.com
> 
> .
>

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CADm-oGmUcMMAkWs6mpCNTS%2BxfieUrKoNBbg8myesXP8AJbq%2Bnw%40mail.gmail.com.

[sqlalchemy] 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

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/949655c4-eacb-46fd-ae68-48a7aa1a3446%40googlegroups.com.

Re: [sqlalchemy] Re: Complex Constraints in Many to Many relationships

[Michael P. McDonnell]

So I must be missing something, but here's what I have right now:

tournament_table = Table(
'tournament',
Base.metadata,
Column('id', UUID(as_uuid=True), primary_key=True))

team_table = Table(
'team',
Base.metadata,
Column('id', UUID(as_uuid=True), primary_key=True,
*Column('tournament_id', UUID(as_uuid=True), ForeignKey('tournament.id
'), nullable=False),*
Column('display_name', String(length=255), nullable=False),
UniqueConstraint('tournament_id', 'display_name'))

team_person_table = Table(
'team_person',
Base.metadata,
*Column('team_id', UUID(as_uuid=True), ForeignKey('team.id
'), primary_key=True*),
*Column('person_id', UUID(as_uuid=True), ForeignKey('person.id
'), primary_key=True)*)

person_table = Table(
'person',
Base.metadata,
Column('id', UUID(as_uuid=True), primary_key=True))


In my current model I can create

Tournament A, Team A (paired to Tournament A), Team B (also paired to
Tournament A), and Team C (also paired to Tournament A)
I can then assign *Person Z* to Team A, Team B and Team C separately
without throwing an integrity exception.

How can I make it so that doesn't happen?


On Wed, Sep 4, 2019 at 4:53 PM Derek Lambert 
wrote:

> If I'm understanding correctly...
>
> You're on the right track. I'd use a composite primary key on
> |team_person|, consisting of foreign keys from |person| and |team|, and
> another composite key (or unique index) on the |team| to |tournament|
> table. This lets the database do all the work.
>
> -Derek
>
> On Wednesday, September 4, 2019 at 4:16:02 PM UTC-5, Michael P. McDonnell
> wrote:
>>
>> Hey -
>> I'm again at a loss of what to google, and as this will ultimately need
>> to be represented in some fashion in sqlalchemy, I figured this is a great
>> place to start:
>>
>> I have a |person| table and a |team| table with a many to many table in
>> between |team_person|.
>> Simple enough!
>>
>> Now - to make it fun.
>> |team| has a relationship to |tournament|
>> How can I prevent a user from joining more than 1 team in a given
>> tournament?
>>
>> I thought about adding a 3rd column to my M2M table,
>> (team_tournament_person), but that could still fail because it could be a
>> team from tournament x, tournament y's ID and a Person Q's ID.
>>
>> So any suggestions on what I should be googling, and then how to
>> implement in SA would be hugely appreciated!
>>
>> Thanks!
>>
> --
> 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+unsubscr...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/d93a10e6-cae8-4346-bae4-99782546becf%40googlegroups.com
> 
> .
>

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHrH0kCuYReaQgCZpAy4T9HkJxV_CFv5wKbTWJDr-x6nUg%40mail.gmail.com.

[sqlalchemy] Re: Complex Constraints in Many to Many relationships

[Derek Lambert]

If I'm understanding correctly...

You're on the right track. I'd use a composite primary key on 
|team_person|, consisting of foreign keys from |person| and |team|, and 
another composite key (or unique index) on the |team| to |tournament| 
table. This lets the database do all the work.

-Derek

On Wednesday, September 4, 2019 at 4:16:02 PM UTC-5, Michael P. McDonnell 
wrote:
>
> Hey - 
> I'm again at a loss of what to google, and as this will ultimately need to 
> be represented in some fashion in sqlalchemy, I figured this is a great 
> place to start:
>
> I have a |person| table and a |team| table with a many to many table in 
> between |team_person|.
> Simple enough!
>
> Now - to make it fun.
> |team| has a relationship to |tournament|
> How can I prevent a user from joining more than 1 team in a given 
> tournament?
>
> I thought about adding a 3rd column to my M2M table, 
> (team_tournament_person), but that could still fail because it could be a 
> team from tournament x, tournament y's ID and a Person Q's ID. 
>
> So any suggestions on what I should be googling, and then how to implement 
> in SA would be hugely appreciated!
>
> Thanks!
>

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/d93a10e6-cae8-4346-bae4-99782546becf%40googlegroups.com.

[sqlalchemy] Complex Constraints in Many to Many relationships

[Michael P. McDonnell]

Hey -
I'm again at a loss of what to google, and as this will ultimately need to
be represented in some fashion in sqlalchemy, I figured this is a great
place to start:

I have a |person| table and a |team| table with a many to many table in
between |team_person|.
Simple enough!

Now - to make it fun.
|team| has a relationship to |tournament|
How can I prevent a user from joining more than 1 team in a given
tournament?

I thought about adding a 3rd column to my M2M table,
(team_tournament_person), but that could still fail because it could be a
team from tournament x, tournament y's ID and a Person Q's ID.

So any suggestions on what I should be googling, and then how to implement
in SA would be hugely appreciated!

Thanks!

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHq%2BCorBMogufyGh6jpX%2BBt6gNpT38oPe4TJ85xDoahHxQ%40mail.gmail.com.