id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
16603	Unnecessary join when using a reverse foreign-key filter and reverse foreign-key aggregate call	Ben Davis	nobody	"(This started as #16554, but it was only half-valid, so I'm re-submitting the valid part here)

Django allows you to perform queries across reverse foreign key relationships. If, however, you need to access that same relationship in a filter ''and'' an aggregate call, the ORM creates unnecessary joins.

== Example: ==
app/models.py
{{{#!python
class Author(models.Model):
    first_name = models.CharField(max_length=30)
    last_name = models.CharField(max_length=30)
    email = models.EmailField()

class Book(models.Model):
    author = models.ForeignKey(Author, related_name='books')
    title = models.CharField(max_length=100)
    genre = models.CharField(max_length=20, choices=(
        ('SCIFI', 'SciFi'),
        ('FANTASY', 'Fantasy'),
        ('NONFICTION', 'NonFiction')
    ))
    published = models.DateField()
    pages = models.IntegerField()
}}}

The django query:
{{{#!python
qs = Author.objects.annotate(pages_written=Sum('books__pages'))
qs = qs.filter(books__genre='SCIFI')
print qs.query
}}}

Generated SQL:
{{{#!sql
SELECT ""app_author"".""id"", ""app_author"".""first_name"", ""app_author"".""last_name"", ""app_author"".""email"", SUM(""app_book"".""pages"") AS ""pages_written"" 
FROM ""app_author"" 
LEFT OUTER JOIN ""app_book"" ON (""app_author"".""id"" = ""app_book"".""author_id"") 
INNER JOIN ""app_book"" T3 ON (""app_author"".""id"" = T3.""author_id"") 
WHERE T3.""genre"" = 'SCIFI'
GROUP BY ""app_author"".""id"", ""app_author"".""first_name"", ""app_author"".""last_name"", ""app_author"".""email""
}}}"	Bug	new	Database layer (models, ORM)	dev	Normal			anssi.kaariainen@…	Accepted	0	0	0	0	0	0