[sqlalchemy] Re: select distinct on a relation to an entity with composite primary key
thank you very much michael, this is some kind of mind spinning sqla expression !! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: select distinct on a relation to an entity with composite primary key
Hi Michael, thank you very much for your prompt answer. What I want to achieve is, counting the number of distinct items, grouped by user_name Given Item1: item_id = 1 service_id = 'test' item_class = 'dummy' Item2: item_id = 2 service_id = 'other' item_class = 'dummy' Invoice1: id = 1 user_name = 'lorem' item = Item1 Invoice2: id = 2 user_name = 'lorem' item = Item1 Invoice3: id = 3 user_name = 'ipsum' item = Item1 Invoice4: id = 4 user_name = 'ipsum' item = Item2 the expected result is: user: lorem, distinct item count : 1 user: ipsum, distinct item count : 2 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: select distinct on a relation to an entity with composite primary key
from sqlalchemy import func, distinct query(Invoice.user_name, func.count(distinct(Item.id))).join(Invoice.items).group_by(Invoice.user_name) On Jan 20, 2011, at 9:51 AM, NiL wrote: Hi Michael, thank you very much for your prompt answer. What I want to achieve is, counting the number of distinct items, grouped by user_name Given Item1: item_id = 1 service_id = 'test' item_class = 'dummy' Item2: item_id = 2 service_id = 'other' item_class = 'dummy' Invoice1: id = 1 user_name = 'lorem' item = Item1 Invoice2: id = 2 user_name = 'lorem' item = Item1 Invoice3: id = 3 user_name = 'ipsum' item = Item1 Invoice4: id = 4 user_name = 'ipsum' item = Item2 the expected result is: user: lorem, distinct item count : 1 user: ipsum, distinct item count : 2 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: select distinct on a relation to an entity with composite primary key
thanks again but the unicity of Item is guaranteed by the triplet of PK I can't just discriminate the distinct() based on the item_id only (it is not unique by itself in my set of data) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: select distinct on a relation to an entity with composite primary key
session.query(Invoice.user_name, Item).join(Invoice.item).distinct().from_self(Invoice.user_name, func.count(1)).group_by(Invoice.user_name) On Jan 20, 2011, at 11:45 AM, NiL wrote: thanks again but the unicity of Item is guaranteed by the triplet of PK I can't just discriminate the distinct() based on the item_id only (it is not unique by itself in my set of data) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.