pythondjangodjango-querysetdjango-q

How to filter a django OR query correctly

I tried to make an OR query with django q. But the number of results is differnt to filter in single queries:

//result is 7
len(Project.objects.filter(Q(moderator='A') | Q(participant='B')))

//result is 6
len(Project.objects.filter(Q(moderator='A')))

//result is 0
len(Project.objects.filter(Q(participant='B')))

since the third query responses a empty queryset, I expect the same results with first and second query. What is here the problem?

I got the same result if I do:

//len is 6
q1 = Project.objects.filter(Q(moderator='A'))

//len is 0
q2 = Project.objects.filter(Q(participant='B'))

//len is 7
q1 | q2

I can resolve this with distinct(). But how can a merge with a querset of 0 produce more results than before?

Solution

  • This happens if both moderator and participant are ManyToManyFields, in that case, it will make two LEFT OUTER JOINs, and the Project object will thus be repeated for each record that matches.

    We can work with .distinct() [Django-doc] to prevent returning the same Project multiple times:

    len(Project.objects.filter(
    Q(moderator='A') | Q(participant='B')
).distinct())

    You can also work with .count() [Django-doc] to count at the database side, which is more efficient than counting at the Django/Python layer:

    Project.objects.filter(
    Q(moderator='A') | Q(participant='B')
).distinct().count()