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
7125	Multiple ForeignKeys to the same model produces wrong SQL statements.	Matthias Kestenholz	Jacob	"Take the following models:

{{{
from django.db import models

class Category(models.Model):
	name = models.CharField(max_length=20)

class Record(models.Model):
	category = models.ForeignKey(Category)

class Relation(models.Model):
	left = models.ForeignKey(Record, related_name='left_set')
	right = models.ForeignKey(Record, related_name='right_set')
}}}

Some example data:

{{{
from testcase.models import *
c1 = Category.objects.create(name='First')
c2 = Category.objects.create(name='Second')

r1 = Record.objects.create(category=c1)
r2 = Record.objects.create(category=c1)
r3 = Record.objects.create(category=c2)
r4 = Record.objects.create(category=c2)

Relation.objects.create(left=r1, right=r2)
Relation.objects.create(left=r3, right=r4)
Relation.objects.create(left=r1, right=r3)
}}}

The following query:
{{{
Relation.objects.filter(left__category__name__in=['Second'], right__category__name__in=['First'])
}}}

produces this SQL statement:
{{{
SELECT ""testcase_relation"".""id"", ""testcase_relation"".""left_id"", ""testcase_relation"".""right_id"" FROM ""testcase_relation""
INNER JOIN ""testcase_record"" ON (""testcase_relation"".""right_id"" = ""testcase_record"".""id"")
INNER JOIN ""testcase_category"" ON (""testcase_record"".""category_id"" = ""testcase_category"".""id"")
INNER JOIN ""testcase_record"" T4 ON (""testcase_relation"".""left_id"" = T4.""id"")
WHERE ""testcase_category"".""name"" IN (First) AND ""testcase_category"".""name"" IN (Second)
}}}

which is obviously wrong.

Note that the first WHERE clause should be T4.name IN (First), not ""testcase_category"".""name"" IN (First). Also note the missing second join on testcase_category."		closed	Database layer (models, ORM)	dev		fixed	qsrf-cleanup wrong sql join intermediary	mk@… cmutel@… omat@… djangoproject.com@…	Accepted	0	0	0	0	0	0