2

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!

user2390182
  • 72,016
  • 6
  • 67
  • 89
Oha Noch
  • 374
  • 1
  • 7
  • 22

1 Answers1

3

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.

MattRowbum
  • 2,162
  • 1
  • 15
  • 20
  • thanks! Im using sqlite3. changing `orderby()` with `order_by()` and `values()` instead of `value_list()` it seems to be working. Something weird happens after though, I try to run a `annotate()` to count and its returning every value in col2 with the count being the amount of duplicates it had with col1 (which were removed successfully). For the example I gave it would give [{col2:a, count: 2}, {col2:s, count: 2}, {col2:a, count: 2}] instead of [{col2:a, count: 2}, {col2:s, count: 1}]. Do you have ideas on why this would happen? – Oha Noch Oct 20 '18 at 14:13
  • I found an answer to this comment at: https://stackoverflow.com/questions/52907276/django-queryset-aggregation-count-counting-wrong-thing#52907353 – Oha Noch Oct 20 '18 at 16:18