Django non-distinct query

Question

So I am making a query that starts as the following:

queryset = FooBar.objects.filter(example__pk__lt=50)

where example is a foreign key. So this will get all FooBar objects that are connected to one of the first fifty Examples. But here is my goal:

I want to have this queryset only include FooBar objects where a different field, lets say target, is not distinct.

I can do that with the following loop:

target_ids = [] #holding a check list for comparison
correct_targets = [] #holding the objects I want to have
for item in queryset: #iteration
    target_ids.append(item.example.pk) #send primary key to check list
    if target_ids.count(item.example.pk) == 2: #check if there has been more than one (if there is more than two I don't send another correct target since I would have already sent it previously)
        correct_targets.append(item) #send correct target

I was hoping that there would be a way to get a queryset of these objects from my queryset without having to loop through. Is this possible?

score 4 · Accepted Answer · answered Jan 24 '14 at 20:40

4

It's possible, using filtering on annotation.

from django.db.models import Count
qs = FooBar.objects.filter(example__pk__lt=50)
qs = qs.annotate(num_examples=Count('example')).filter(num_examples__gt=1)

answered Jan 24 '14 at 20:40

vartec

131,205
36
218
244

from django 3.2 you can use (and it's better to do) alias instead of annotate in examples like this. (https://docs.djangoproject.com/en/3.2/ref/models/querysets/#alias) – Seyed Mostafa SeyedAshoor Sep 08 '22 at 12:31

score 1 · Answer 2 · answered Jan 24 '14 at 20:36

You could use an aggregate query to get the list of nondistinct items and then use that to filter your other query.

nondistinct = Example.objects.values('target').annotate(Count('id')).filter(id__count__gt=1)
Foobar.objects.filter(example__pk__lt=50).filter(example__target__in=[item['target'] for item in nondistinct])

score 0 · Answer 3 · answered Jan 24 '14 at 19:43

I am not sure if "reverse distinct" is possible with Django ORM, or not even underlying SQL database.

However the following might be possible

Query all items to queryset q1
Query distict items to queryset q2
Take the difference qs_not_distict = qs1.exclude(pk__in=qs2)

score 0 · Answer 4 · answered Feb 22 '21 at 14:22

Just annotate on any non-dictinct field value from your FK model:

from django.db.models import F
....

qs = FooBar.objects.filter(example__pk__lt=50).annotate(example_target=F(example__target))

This query will contain duplicated FooBar objects, one object for each new target value.

Django non-distinct query

4 Answers4