Reverse ForeignKey or ManyToMany "AND" query - issue??


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.


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:

  1. Multiple Arguments within the filter.
Author.objects.filter(entry__headline__contains="Pop", entry__pub_date__year=2023)
  1. 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)
  1. 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)


  1. Why is this way different like the two previous ones?
  2. 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(Q(headline__contains="Pop") & Q(pub_date__year=2023))

Entry.objects.filter(headline__contains="Pop") & Entry.objects.filter(pub_date__year=2023)


I don’t have answers for you based on actual knowledge, just some thoughts on this that make sense to me.

Would I have initially expected this to be the results?

  • No

Does this surprise me?

  • No

Does this make sense to me?

  • Yes

I don’t have anywhere near the amount of knowledge of the ORM needed to really analyze this from the perspective of the code.

But, logically, this makes sense to me. I think these results are quite reasonable.

A query of the form MyModel.objects.filter(field_a=1, field_b=2) seems to me like it would need to issue one expression to resolve this.
However, the alternate form, MyModel.objects.filter(field_a=1) & MyModel.objects.filter(field_b=2) seems to me like it would need to generate (internally) two separate expressions that then need to be resolved with a logical and to generate the queryset.

In the case of the example you provide, the first expression (entry__headline__contains='Pop') is going to retrieve two rows for Author “Juan”, Pop 1 and Pop 3. that result set of Author is then joined with the results of the second expression for “2023”. Since “Juan” does have a pub date in 2023, both of those results of the first expression are going to pass the second expression.

Such is the fun of handling reverse FK relationships in SQL…

1 Like

Thanks Ken!
It makes sense.

Such is the fun of handling reverse FK relationships in SQL…

It is indeed :stuck_out_tongue_winking_eye:

Thanks we have Q objects :muscle: