Hello,
I was trying some “AND” queries and have the following doubt.
In a forward query you can do this:
Model.objects.filter(x=1) & Model.objects.filter(y=2)
Model.objects.filter(x=1, y=2)
Model.objects.filter(Q(x=1) & Q(y=2))
All give the same result: a QuerySet that satisfies both conditions.
But If I have a Reverse ForeignKey or ManyToMany “AND” query something different happens.
Example:
class Blog(models.Model):
name = models.CharField(max_length=100)
tagline = models.TextField()
class Author(models.Model):
name = models.CharField(max_length=100)
country = models.ForeignKey(Country, on_delete=models.CASCADE)
class Entry(models.Model):
blog = models.ForeignKey(Blog, on_delete=models.CASCADE)
headline = models.CharField(max_length=255)
body_text = models.TextField()
pub_date = models.DateField()
authors = models.ManyToManyField(Author)
Let´s say we have this data.
| Headline | Pub Date Year | Authors | Blog |
|---|---|---|---|
| Pop 1 | 2023 | Juan | Pop |
| Pop 2 | 2023 | Emma | Pop |
| Pop 3 | 2010 | Juan, Walmer | Pop |
| Other | 2023 | John | Beatles |
I want to make an “AND” query to get the authors that satisfy both the headline that contains the word “Pop” and Pub Date Year equal to 2023.
- Expected result: Juan and Emma
| Headline | Pub Date Year | Authors | Blog |
|---|---|---|---|
| Pop 1 | 2023 | Juan | Pop |
| Pop 2 | 2023 | Emma | Pop |
We have the following methods:
- Multiple Arguments within the filter.
Author.objects.filter(entry__headline__contains="Pop", entry__pub_date__year=2023)
- Q object
Author.objects.filter(Q(entry__headline__contains="Pop") & Q(entry__pub_date__year=2023))
→ These two give the same answer (the expected result: Juan and Emma) and create the same SQL.
SELECT "blog_author"."id", "blog_author"."name", "blog_author"."email", "blog_author"."country_id"
FROM "blog_author"
INNER JOIN "blog_entry_authors"
ON ("blog_author"."id" = "blog_entry_authors"."author_id")
INNER JOIN "blog_entry"
ON ("blog_entry_authors"."entry_id" = "blog_entry"."id")
WHERE ("blog_entry"."headline" LIKE %Pop% ESCAPE '\' AND "blog_entry"."pub_date" BETWEEN 2023-01-01 AND 2023-12-31)
- But with this third option the result is different. And I don’t understand why.
Author.objects.filter(entry__headline__contains="Pop") & Author.objects.filter(entry__pub_date__year=2023)
The result is different, it is: Juan, Emma, Juan.
I thought this should also be: Juan and Emma.
But why it isn’t?
This is the SQL created:
SELECT "blog_author"."id", "blog_author"."name", "blog_author"."email", "blog_author"."country_id"
FROM "blog_author"
INNER JOIN "blog_entry_authors"
ON ("blog_author"."id" = "blog_entry_authors"."author_id")
INNER JOIN "blog_entry"
ON ("blog_entry_authors"."entry_id" = "blog_entry"."id")
LEFT OUTER JOIN "blog_entry_authors" T4
ON ("blog_author"."id" = T4."author_id")
LEFT OUTER JOIN "blog_entry" T5 ON (T4."entry_id" = T5."id")
WHERE ("blog_entry"."headline" LIKE %Pop% ESCAPE '\' AND T5."pub_date" BETWEEN 2023-01-01 AND 2023-12-31)
>>>
QUESTION:
- Why is this way different like the two previous ones?
- Is this the expected result (Juan, Emma, Juan?)
If you have a Forward Query, all the three options work the same way.
Entry.objects.filter(headline__contains="Pop",pub_date__year=2023)
Entry.objects.filter(Q(headline__contains="Pop") & Q(pub_date__year=2023))
Entry.objects.filter(headline__contains="Pop") & Entry.objects.filter(pub_date__year=2023)
THANKS.
Guzmán.
