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.
