CODEWITHSUNDEEP
pythondjango
AnikiAliz
AnikiAliz

Reputation: 13

Very weird behaviour concerning ManyToMany filtering and combining querysets

Calendars have a owner and have a ManyToMany field 'assistants' i have a Calendar who has 2 assistants one of which is its owner. I think these 3 lines of code in the django shell can explain the weird behaviour quite well.

In [17]: Calendar.objects.filter(assistants=customer).exclude(owner=customer)                             
Out[17]: <QuerySet []>
In [20]: Calendar.objects.filter(owner=customer)                                                          
Out[20]: <QuerySet [<Calendar: aliz cal>, <Calendar: yassi has a calendar>]>
In [19]: Calendar.objects.filter(owner=customer) | Calendar.objects.filter(assistants=customer).exclude(owner=customer)                                                                                    
Out[19]: <QuerySet [<Calendar: aliz cal>, <Calendar: aliz cal>, <Calendar: yassi has a calendar>]>

Of course expected the result of queryset join to be the actual union of them.

Upvotes: 1

Views: 46

Answers (2)

CoffeeBasedLifeform
CoffeeBasedLifeform

Reputation: 2921

Assuming this is for django 1.11+:
| does not represent a union. It represents an OR-combination (which maintains all joins; hence aliz showing up twice) of two querysets .

qs1.filter(x=1) | qs2.exclude(x=1) translates to:

SELECT STUFF FROM TABLES_AND_JOINS WHERE (x = 1 OR NOT (x = 1))

While qs1.filter(x=1).union(qs2.exclude(x=1)) translates to:

SELECT STUFF FROM TABLE1 WHERE x = 1 UNION SELECT STUFF FROM TABLE2 WHERE NOT x = 1

Use str(qs.query) to see the SQL.

Upvotes: 1

Endre Both
Endre Both

Reputation: 5740

What you are doing is OR-ing the WHERE clauses of both queries, which is different from a union (and a bit tricky when joining is involved; here, the ORM switches from an inner join in queryset #1 to an outer join in queryset #3 to account for the second query that has no joins). See the relevant docs.

Try union(), available from Django 1.11 onwards:

qs1 = Calendar.objects.filter(assistants=customer).exclude(owner=customer)                             
qs2 = Calendar.objects.filter(owner=customer) 
qs3 = qs1.union(qs2)

Upvotes: 0

Related Questions