Django "AND" query: Reverse ForeignKey or ManyToMany giving an unexpected result

I found a different result on an "AND" query depending if it was a reverse of forward query.

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 (x=1 and y=2).

But If I have a REVERSE FOREIGNKEY or MANY TO MANY “AND” query something different happens.

---

This is what I did and expect.

I have these models:

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)

And let's say I have this data:

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.

We can use the following methods:

1. Multiple Arguments within the filter.

Author.objects.filter(entry__headline__contains="Pop", entry__pub_date__year=2023)

2. 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)

3. HERE COMES THE PROBLEM: 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, the AND query gets this authors: Juan, Emma, Juan. I thought this should also be: Juan and Emma.

But why it isn't?

This is the SQL created (which is different than the previous two)

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)
>>>

QUESTIONS:

1. Why does the third option give a different result than the two previous ones?
2. Is this the expected result (Juan, Emma, Juan?)

Back to Top