Join reuse on reverse fk chained queries

Using Django Using the ORM
1

When recently debugging a query, I noticed that when doing a query on a lookup for a reverse many to one relationship the join isn’t reused, which causes a double JOIN on the final SQL causing repeated results.

Example using the Book/Author example:

Author.objects.filter(book__title='Example', book__year=2020) # Only a single reused JOIN
# Returns a different result than
Author.objects.filter(Book__title='Example').filter(book__year=2020) # Two joins generated on the final SQL query

Moreover appending a .count() to the end of the query will result on a different outcome, for a single author/book pair, the first one will return 1 and the second 2.

While investigating the issue, I’ve noticed there’s actually a test case covering that here: https://github.com/django/django/blob/main/tests/queries/tests.py#L3858

Why is it like that? Should chained filters like that behave just like adding them together?

2

:wave: @renatoalencar

This is explained in details in the Spanning multi-valued relationships section of the documentation and is a common source of confusion but can’t be changed for obvious backward compatibility reasons.

If you want to reuse a join across filter calls your best bet is likely to use a FilteredRelation annotation instead.

1 Like