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
23440	select_related with multiple ForeignKey to same table generates sub-optimal SQL	djbug	nobody	"I have this model where Relation has 2 foreign keys, both to Node. 

{{{
class Node(models.Model):
    name = models.CharField(max_length=25)

class Relation(models.Model):
    left = models.ForeignKey(Node)
    right = models.ForeignKey(Node, related_name='right')

}}}

I've shown a sample query along with the SQL that's generated by Django:

{{{
Relation.objects.select_related().filter(id=1)
}}}

{{{#!sql
SELECT ""relation"".""id"", ""relation"".""left_id"", ""relation"".""right_id"", ""left"".""id"", ""left"".""name"", T3.""id"", T3.""name"", 
FROM ""relation"" INNER JOIN ""left"" ON ( ""relation"".""left_id"" = ""left"".""id"" ) INNER JOIN ""right"" T3 ON (""relation"".""right_id"" = T3.""id"" ) 
WHERE ""relation"".""id"" = 1
}}}

There are 2 INNER JOIN happening above. However we can do with just 1 INNER JOIN, since left & right are pointing to the same table. See below:


{{{
#!sql
SELECT ""relation"".""id"", ""relation"".""left_id"", ""relation"".""right_id"", ""left"".""id"", ""left"".""name"", 
FROM ""relation"" INNER JOIN ""left"" ON ( ""relation"".""left_id"" = ""left"".""id"" OR ""relation"".""right_id"" = ""left"".""id"")
WHERE ""relation"".""id"" = 1
}}}

P.S. Not sure if this is related : [7125]"	Cleanup/optimization	closed	Uncategorized	1.6	Normal	invalid	select_related		Unreviewed	0	0	0	0	0	0