Re: [sqlalchemy] Re: select distinct on a relation to an entity with composite primary key

[Michael Bayer]

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.

Re: [sqlalchemy] Re: select distinct on a relation to an entity with composite primary key

[Michael Bayer]

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.