CODEWITHSUNDEEP
pythondjangodjango-querysetdjango-orm
Oha Noch
Oha Noch

Reputation: 404

Django queryset get distinct column values with respect to other column

I am using django orm and I am trying to get all the values of a column, but only if a different column is unique with respect to it. Its hard to explain, so here is an example:

q | a | 1    
w | s | 2  
e | a | 3  
q | a | 4  
w | s | 5  
e | a | 6

I would like to get all the values that are in column 2 but if they also have the same value in column 1 don't take duplicates. So in this case I want to get [a,s,a]. (column 3 only serves to show why these duplicates don't get merged in the first place).

What I tried: I tried grouping by values of columns 1 and 2 and taking the distinct value of it, thus I would end up with:
q | a
w | s ---> which is actually given as [(q,a),(w,s),(e,a)]
e | a
with the code: queryset.values(col1,col2).distinct() but I only want the second column so I then added .values(col2). The problem with that is the distinct gets applied to the results of the second values as well as the first, so instead of getting [a,s,a] I get [a,s].
I also tried using .defer() so queryset.values(col1,col2).distinct().defer(col1) but apparently you can't use .defer() after using .values().

I can't find a solution to this online and am getting nowhere for a while now, any help would be greatly appreciated!

Upvotes: 2

Views: 2351

Answers (1)

MattRowbum
MattRowbum

Reputation: 2192

If you are using PostgreSQL as your database, try this:

queryset.order_by('col1', 'col2').distinct('col1', 'col2').values('col2')

I haven't had a chance to test it, but it should find results where the combination of col1 and col2 are distinct, and return the col2 values.

Upvotes: 4

Related Questions