Link multiple filters () in Django, is that a mistake?

Question

I always assumed that the chain of calls with multiple filters () in Django was always the same as collecting them in one call.

# Equivalent Model.objects.filter(foo=1).filter(bar=2) Model.objects.filter(foo=1,bar=2) 

but I ran a complex query in my code where this is not the case.

 class Inventory(models.Model): book = models.ForeignKey(Book) class Profile(models.Model): user = models.OneToOneField(auth.models.User) vacation = models.BooleanField() country = models.CharField(max_length=30) # Not Equivalent! Book.objects.filter(inventory__user__profile__vacation=False).filter(inventory__user__profile__country='BR') Book.objects.filter(inventory__user__profile__vacation=False, inventory__user__profile__country='BR') 

Generated SQL

 SELECT "library_book"."id", "library_book"."asin", "library_book"."added", "library_book"."updated" FROM "library_book" INNER JOIN "library_inventory" ON ("library_book"."id" = "library_inventory"."book_id") INNER JOIN "auth_user" ON ("library_inventory"."user_id" = "auth_user"."id") INNER JOIN "library_profile" ON ("auth_user"."id" = "library_profile"."user_id") INNER JOIN "library_inventory" T5 ON ("library_book"."id" = T5."book_id") INNER JOIN "auth_user" T6 ON (T5."user_id" = T6."id") INNER JOIN "library_profile" T7 ON (T6."id" = T7."user_id") WHERE ("library_profile"."vacation" = False AND T7."country" = BR ) SELECT "library_book"."id", "library_book"."asin", "library_book"."added", "library_book"."updated" FROM "library_book" INNER JOIN "library_inventory" ON ("library_book"."id" = "library_inventory"."book_id") INNER JOIN "auth_user" ON ("library_inventory"."user_id" = "auth_user"."id") INNER JOIN "library_profile" ON ("auth_user"."id" = "library_profile"."user_id") WHERE ("library_profile"."vacation" = False AND "library_profile"."country" = BR ) 

The first set of filter() chain requests filter() connects the Inventory model twice, creating an OR between the two conditions, while the second set of AND queries combines the two conditions. I expected that the first request would also be two conditions. Is this the expected behavior or is it a bug in Django?

The answer to the corresponding question is whether there is a drawback in using .filter (). filter (). filter () ... "in Django? seems to indicate that the two queries should be equivalent.

Answer 1

As far as I understand, they are slightly different in design (and I'm certainly open to fixing): filter(A, B) first filters by A, and then the subfilter by B, and filter(A).filter(B) will return the string that matches A 'and' is potentially another string that matches B.

Take a look at an example here:

https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships

in particular:

Everything inside one filter () call is applied at the same time to filter out elements that meet all of these requirements. Successive calls to filter () further limit the set of objects

...

In the second example (filter (A) .filter (B)), the first filter limited the set of queries (A). The second filter restricted the set of blogs to those that are also (B). The records selected by the second filter may or may not match the records in the first filter. ''

Answer 2

These two filtering styles are equivalent in most cases, but when the query on objects is based on ForeignKey or ManyToManyField, they are slightly different.

Examples from the documentation .

model
An Entry Blog is a one-to-many relationship.

 from django.db import models class Blog(models.Model): ... class Entry(models.Model): blog = models.ForeignKey(Blog) headline = models.CharField(max_length=255) pub_date = models.DateField() ... 

objects
Assuming there is a blog and input objects here.

inquiries

 Blog.objects.filter(entry__headline_contains='Lennon', entry__pub_date__year=2008) Blog.objects.filter(entry__headline_contains='Lennon').filter( entry__pub_date__year=2008) 

For the first request (one filter one), it matches only blog1.

For the second request (with code filters one), it filters blog1 and blog2.
The first filter restricts the set of queries for blog1, blog2, and blog5; the second filter restricts the set of blogs beyond blog1 and blog2.

And you must understand that

We filter Blog elements with each filter statement, not Entry elements.

So, this is not the same, because Blog and Entry are a multi-valued relationship.

Link: https://docs.djangoproject.com/en/1.8/topics/db/queries/#spanning-multi-valued-relationships
If something is wrong, please correct me.

Edit: Changed v1.6 to v1.8, since links 1.6 are no longer available.

Answer 3

As you can see in the generated SQL statements, the difference is not “OR,” as some may suspect. This is where WHERE and JOIN are placed.

Example1 (same combined table):

(example from https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships )

 Blog.objects.filter(entry__headline__contains='Lennon', entry__pub_date__year=2008) 

This will give you all blogs that have one entry with both (entry_headline_contains = 'Lennon') AND (entry__pub_date__year = 2008), what do you expect from this request. Result: Book from {entry.headline: "The Life of Lennon", entry.pub_date: '2008'}

Example 2 (chained)

 Blog.objects.filter(entry__headline__contains='Lennon').filter(entry__pub_date__year=2008) 

This will cover all the results from Example 1, but it will give a slightly larger result. Since it first filters all blogs using (entry_headline_contains = 'Lennon') and then from the result filters (entry__pub_date__year = 2008).

The difference is that it will also give you results, such as: Book with {entry.headline: ' Lennon ', entry.pub_date: 2000}, {entry.headline: 'Bill', entry.pub_date: 2008 }

In your case

I think this is the one you need:

 Book.objects.filter(inventory__user__profile__vacation=False, inventory__user__profile__country='BR') 

And if you want to use OR, please read: https://docs.djangoproject.com/en/dev/topics/db/queries/#complex-lookups-with-q-objects

Answer 4

Sometimes you don’t want to combine multiple filters like this:

 def your_dynamic_query_generator(self, event: Event): qs \ .filter(shiftregistrations__event=event) \ .filter(shiftregistrations__shifts=False) 

And the following code will not actually return the correct thing.

 def your_dynamic_query_generator(self, event: Event): return Q(shiftregistrations__event=event) & Q(shiftregistrations__shifts=False) 

What you can do now is use the annotation filter.

In this case, we take into account all shifts that relate to a specific event.

 qs: EventQuerySet = qs.annotate( num_shifts=Count('shiftregistrations__shifts', filter=Q(shiftregistrations__event=event)) ) 

After that you can filter by annotation.

 def your_dynamic_query_generator(self): return Q(num_shifts=0) 

This solution is also cheaper for large query sets.

Hope this helps.