after queryset refactor, specifying > 2 OR'ed conditions that span many-to-many relationships returns broken SQL
For a prettier code view, see http://dpaste.com/hold/51756/
class Person(models.Model):
name = models.CharField(max_length=100)
def __unicode__(self):
return self.name
class Group(models.Model):
name = models.CharField(max_length=100)
parent = models.ForeignKey('Group', null=True, related_name='children')
def __unicode__(self):
return self.name
class Node(models.Model):
name = models.CharField(max_length=100)
group = models.ForeignKey(Group, related_name='nodes')
people = models.ManyToManyField(Person, related_name='nodes')
def __unicode__(self):
return self.name
p = Person.objects.create(name='Joe')
g = Group.objects.create(name='Group 1')
# this looks good:
nodes = p.nodes.filter( Q(group=g) | Q(group__children=g) )
"""
SELECT "smart_node"."id",
"smart_node"."name",
"smart_node"."group_id"
FROM "smart_node"
LEFT OUTER JOIN "smart_node_people"
ON ("smart_node"."id" = "smart_node_people"."node_id")
INNER JOIN "smart_group"
ON ("smart_node"."group_id" = "smart_group"."id")
LEFT OUTER JOIN "smart_group" T5
ON ("smart_group"."id" = T5."parent_id")
WHERE "smart_node_people"."person_id" = 2
AND ("smart_node"."group_id" = 1
OR T5."id" = 1)
"""
# where is the last condition ???
nodes = p.nodes.filter( Q(group=g) | Q(group__children=g) | Q(group__children__children=g) )
"""
SELECT "smart_node"."id",
"smart_node"."name",
"smart_node"."group_id"
FROM "smart_node"
LEFT OUTER JOIN "smart_node_people"
ON ("smart_node"."id" = "smart_node_people"."node_id")
INNER JOIN "smart_group"
ON ("smart_node"."group_id" = "smart_group"."id")
LEFT OUTER JOIN "smart_group" T5
ON ("smart_group"."id" = T5."parent_id")
WHERE "smart_node_people"."person_id" = 2
AND ("smart_node"."group_id" = 1
OR T5."id" = 1)
"""
# it's not a problem with children__children, as it works fine here:
nodes = p.nodes.filter( Q(group=g) | Q(group__children__children=g) )
"""
SELECT "smart_node"."id",
"smart_node"."name",
"smart_node"."group_id"
FROM "smart_node"
LEFT OUTER JOIN "smart_node_people"
ON ("smart_node"."id" = "smart_node_people"."node_id")
INNER JOIN "smart_group"
ON ("smart_node"."group_id" = "smart_group"."id")
LEFT OUTER JOIN "smart_group" T5
ON ("smart_group"."id" = T5."parent_id")
LEFT OUTER JOIN "smart_group" T6
ON (T5."id" = T6."parent_id")
WHERE "smart_node_people"."person_id" = 2
AND ("smart_node"."group_id" = 1
OR T6."id" = 1)
"""
# specifying the second two conditions in reverse order seems to fix it!
nodes = p.nodes.filter( Q(group=g) | Q(group__children__children=g) | Q(group__children=g) )
"""
SELECT "smart_node"."id",
"smart_node"."name",
"smart_node"."group_id"
FROM "smart_node"
LEFT OUTER JOIN "smart_node_people"
ON ("smart_node"."id" = "smart_node_people"."node_id")
INNER JOIN "smart_group"
ON ("smart_node"."group_id" = "smart_group"."id")
LEFT OUTER JOIN "smart_group" T5
ON ("smart_group"."id" = T5."parent_id")
LEFT OUTER JOIN "smart_group" T6
ON (T5."id" = T6."parent_id")
WHERE "smart_node_people"."person_id" = 2
AND ("smart_node"."group_id" = 1
OR T6."id" = 1
OR T5."id" = 1)
"""
# however, adding one more condition breaks it again (T6 is missing from OR statements):
nodes = p.nodes.filter( Q(group=g) | Q(group__children__children__children=g) | Q(group__children__children=g) | Q(group__children=g) )
"""
SELECT "smart_node"."id",
"smart_node"."name",
"smart_node"."group_id"
FROM "smart_node"
LEFT OUTER JOIN "smart_node_people"
ON ("smart_node"."id" = "smart_node_people"."node_id")
INNER JOIN "smart_group"
ON ("smart_node"."group_id" = "smart_group"."id")
LEFT OUTER JOIN "smart_group" T5
ON ("smart_group"."id" = T5."parent_id")
LEFT OUTER JOIN "smart_group" T6
ON (T5."id" = T6."parent_id")
LEFT OUTER JOIN "smart_group" T7
ON (T6."id" = T7."parent_id")
WHERE "smart_node_people"."person_id" = 2
AND ("smart_node"."group_id" = 1
OR T7."id" = 1
OR T5."id" = 1)
"""
I am also having trouble OR'ing query sets that span more than 2 tables as I have explained here:
http://groups.google.com/group/django-users/browse_thread/thread/48cd89cd0ab099e1
This may be a different issue, as I am getting KeyError.
This line, in django.db.query (line 321 svn version 7547):
promote = (rhs.alias_map[alias][JOIN_TYPE] == self.LOUTER)
complains that the second extra table's name is not in the rhs.alias_map dictionary.
I may open a seperate ticket or post the details here.