Why does Django QuerySet produce a query with 2 inner joins with AND clauses, instead or a single inner join with OR, when chaining filters?

Question

I'm following the Django docs on making queries, and this example showed up:

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

I was hoping that the corresponding SQL query to this involved only one inner join and a OR clause, since the corresponding results are entries that either satisfy one or both of the conditions.

Nevertheless, this is what an inspection to the queryset's query returned:

>>> qs = Blog.objects.filter(entry__headline__contains='Lennon').filter(entry__pub_date__year=2008)
>>> print(qs.query)
SELECT "blog_blog"."id", "blog_blog"."name", "blog_blog"."tagline"
FROM "blog_blog" 
INNER JOIN "blog_entry" ON ("blog_blog"."id" = "blog_entry"."blog_id") 
INNER JOIN "blog_entry" T3 ON ("blog_blog"."id" = T3."blog_id") 
WHERE ("blog_entry"."headline" LIKE %Lennon% ESCAPE '\' 
AND T3."pub_date" BETWEEN 2008-01-01 AND 2008-12-31)

You can see that it makes TWO inner joins.

Wouldn't the result be the same as:

SELECT "blog_blog"."id", "blog_blog"."name", "blog_blog"."tagline" 
FROM "blog_blog" 
INNER JOIN "blog_entry" ON ("blog_blog"."id" = "blog_entry"."blog_id")
WHERE ("blog_entry"."headline" LIKE %Lennon% ESCAPE '\' OR "blog_entry"."pub_date" BETWEEN 2008-01-01 AND 2008-12-31)

?

And this query is faster.

score 1 · Accepted Answer · answered May 08 '20 at 04:32

Well, after some research I've learned a few things.

First, from this answer, the way to implement an OR query is:

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

And, from this fiddling:

https://www.db-fiddle.com/f/f8SGzTLeyr7DNZUaCx9HVL/0

I realized that the two queries in the OP don't get the same results: in the 2 inner joins, there is a cartesian product of results (2 "Lennon" * 2 "2008"), while in the second one there are 3 results (and it's indeed faster).

score 0 · Answer 2 · answered May 08 '20 at 02:30

0

I assume, it is because you do filter() twice.

Try

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

answered May 08 '20 at 02:30

Tigran

632
1
5
21

This will AND conditions. – Niloct May 08 '20 at 03:00

Why does Django QuerySet produce a query with 2 inner joins with AND clauses, instead or a single inner join with OR, when chaining filters?

2 Answers2