Chaining multiple filter in Django is this a bug

Question

I always assumed that chaining multiple filter   calls in Django was always the same as collecting them in a single call     Equivalent Model objects filter foo 1  filter bar 2  Model objects filter foo 1 bar 2    but I have run across a complicated queryset 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     The generated SQL is  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 queryset with the chained filter   calls joins the Inventory model twice effectively creating an OR between the two conditions whereas the second queryset ANDs the two conditions together  I was expecting that the first query would also AND the two conditions  Is this the expected behavior or is this a bug in Django   The answer to a related question Is there a downside to using  quot  filter   filter   filter      quot  in Django  seems to indicated that the two querysets should be equivalent

User · Answer

As you can see in the generated SQL statements the difference is not the "OR" as some may suspect. It is how the WHERE and JOIN is placed.

Example1 (same joined 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 the Blogs that have one entry with both (entry_headline_contains='Lennon') AND (entry__pub_date__year=2008), which is what you would expect from this query. Result: Book with {entry.headline: '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 generate slightly more result. Because it first filters all the blogs with (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 like: Book with {entry.headline: 'Lennon', entry.pub_date: 2000}, {entry.headline: 'Bill', entry.pub_date: 2008}

In your case

I think it is this 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

User · Answer

From Django docs    To handle both of these situations  Django has a consistent way of processing filter   calls  Everything inside a single filter   call is applied simultaneously to filter out items matching all those requirements  Successive filter   calls further restrict the set of objects  but for multi-valued relations  they apply to any object linked to the primary model  not necessarily those objects that were selected by an earlier filter   call    It is clearly said that multiple conditions in a single filter   are applied simultaneously  That means that doing    objs   Mymodel objects filter a True  b False   will return a queryset with raws from model Mymodel where a True AND b False   Successive filter    in some case  will provide the same result  Doing    objs   Mymodel objects filter a True  filter b False   will return a queryset with raws from model Mymodel where a True AND b False too  Since you obtain  quot first quot  a queryset with records which have a True and then it s restricted to those who have b False at the same time   The difference in chaining filter   comes when there are multi-valued relations  which means you are going through other models  such as the example given in the docs  between Blog and Entry models   It is said that in that case       they apply to any object linked to the primary model  not necessarily those objects that were selected by an earlier filter   call   Which means that it applies the successives filter   on the target model directly  not on previous filter   If I take the example from the docs   Blog objects filter entry  headline  contains  Lennon   filter entry  pub date  year 2008   remember that it s the model Blog that is filtered  not the Entry  So it will treat the 2 filter   independently  It will  for instance  return a queryset with Blogs  that have entries that contain  Lennon   even if they are not from 2008  and entries that are from 2008  even if their headline does not contain  Lennon   THIS ANSWER goes even further in the explanation  And the original question is similar

User · Answer

These two style of filtering are equivalent in most cases  but when query on objects base on ForeignKey or ManyToManyField  they are slightly different  Examples from the documentation  model Blog to Entry is a one-to-many relation  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 are some blog and entry objects here   queries 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 1st query  single filter one   it match only blog1  For the 2nd query  chained filters one   it filters out blog1 and blog2  The first filter restricts the queryset to blog1  blog2 and blog5  the second filter restricts the set of blogs further to blog1 and blog2  And you should realize that  We are filtering the Blog items with each filter statement  not the Entry items   So  it s not the same  because Blog and Entry are multi-valued relationships  Reference   https   docs djangoproject com en 1 8 topics db queries  spanning-multi-valued-relationships If there is something wrong  please correct me  Edit  Changed v1 6 to v1 8 since the 1 6 links are no longer available

User · Answer

The way I understand it is that they are subtly different by design  and I am certainly open for correction   filter A  B  will first filter according to A and then subfilter according to B  while filter A  filter B  will return a row that matches A  and  a potentially different row that matches B   Look at the example here    https   docs djangoproject com en dev topics db queries  spanning-multi-valued-relationships  particularly      Everything inside a single filter   call is applied simultaneously to filter out items matching all those requirements  Successive filter   calls further restrict the set of  objects           In this second example  filter A  filter B    the first filter restricted the queryset to  A   The second filter restricted the set of blogs further to those that are also  B   The entries select by the second filter may or may not be the same as the entries in the first filter

User · Answer

Saw this in a comment and I thought it was the simplest explanation  filter A  B  is the AND filter A  filter B  is OR

User · Answer

Sometimes you don t want to join multiple filters together like this   def your dynamic query generator self  event  Event       qs        filter shiftregistrations  event event         filter shiftregistrations  shifts False    And the following code would actually not return the correct thing   def your dynamic query generator self  event  Event       return Q shiftregistrations  event event   amp  Q shiftregistrations  shifts False    What you can do now is to use an annotation count-filter   In this case we count all shifts which belongs to a certain event   qs  EventQuerySet   qs annotate      num shifts Count  shiftregistrations  shifts   filter Q shiftregistrations  event event       Afterwards you can filter by annotation   def your dynamic query generator self       return Q num shifts 0    This solution is also cheaper on large querysets   Hope this helps

[django] Chaining multiple filter() in Django, is this a bug?

Example1 (same joined table) :

Example 2 (chained)

In your case

Examples related to django

Examples related to django-orm