CODEWITHSUNDEEP
pythondjangodjango-queryset
Ryan Saxe
Ryan Saxe

Reputation: 17869

Django non-distinct query

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?

Upvotes: 2

Views: 2809

Answers (4)

Yaroslav Varkhol
Yaroslav Varkhol

Reputation: 967

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.

Upvotes: 0

vartec
vartec

Reputation: 134731

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)

Upvotes: 4

Vinod Kurup
Vinod Kurup

Reputation: 2776

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])

Upvotes: 1

Mikko Ohtamaa
Mikko Ohtamaa

Reputation: 83706

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

However the following might be possible

  1. Query all items to queryset q1

  2. Query distict items to queryset q2

  3. Take the difference qs_not_distict = qs1.exclude(pk__in=qs2)

Upvotes: 0

Related Questions