#14055: Wrong query generated when using reverse foreign key
------------------------------------------+---------------------------------
Reporter: premalshah | Owner: nobody
Status: new | Milestone: 1.3
Component: Database layer (models, ORM) | Version: 0.96
Keywords: foreign key | Stage: Unreviewed
Has_patch: 0 |
------------------------------------------+---------------------------------
Here is the model setup
{{{
from django.db import models
class Base(models.Model):
id = models.AutoField(primary_key=True)
field1 = models.CharField(max_length=11)
class Child(models.Model):
id = models.AutoField(primary_key=True)
obj = models.ForeignKey(Base)
field1 = models.CharField(max_length=11)
field2 = models.IntegerField()
}}}
Here are some sample queries and the corresponding sql statements
generated and sql statements expected
{{{
'''Query 1'''
Base.objects.filter(child__obj=1).query
''Generated SQL''
SELECT `apptest_base`.`id`, `apptest_base`.`field1` FROM `apptest_base`
WHERE `apptest_base`.`id` = 1;
''Expected SQL''
SELECT `apptest_base`.`id`, `apptest_base`.`field1` FROM `apptest_base`
INNER JOIN `apptest_child`
ON (`apptest_base`.`id` = `apptest_child`.`obj_id`) WHERE
`apptest_child`.`obj_id` = 1;
}}}
{{{
'''Query 2'''
Base.objects.filter(child__obj=1, child__field1='a').query
''Generated SQL''
SELECT `apptest_base`.`id`, `apptest_base`.`field1` FROM `apptest_base`
INNER JOIN `apptest_child` T4
ON (`apptest_base`.`id` = T4.`obj_id`) WHERE (`apptest_base`.`id` = 1 AND
T4.`field1` = a );
''Expected SQL''
SELECT `apptest_base`.`id`, `apptest_base`.`field1` FROM `apptest_base`
INNER JOIN `apptest_child`
ON (`apptest_base`.`id` = `apptest_child`.`obj_id`) WHERE
(`apptest_child`.`obj_id` = 1 AND `apptest_child`.`field1` = a );
}}}
{{{
'''Query 3'''
Base.objects.filter(child__obj=1,
child__field1='a').order_by('child__field2', 'child__obj').query
''Generated SQL''
SELECT `apptest_base`.`id`, `apptest_base`.`field1` FROM `apptest_base`
LEFT OUTER JOIN `apptest_child`
ON (`apptest_base`.`id` = `apptest_child`.`obj_id`) INNER JOIN
`apptest_child` T4
ON (`apptest_base`.`id` = T4.`obj_id`) WHERE (`apptest_base`.`id` = 1 AND
T4.`field1` = a )
ORDER BY `apptest_child`.`field2` ASC, `apptest_base`.`id` ASC;
''Expected SQL''
SELECT `apptest_base`.`id`, `apptest_base`.`field1` FROM `apptest_base`
LEFT OUTER JOIN `apptest_child`
ON (`apptest_base`.`id` = `apptest_child`.`obj_id`) WHERE
(`apptest_child`.`obj_id` = 1 AND
`apptest_child`.`field1` = a ) ORDER BY `apptest_child`.`field2` ASC,
`apptest_child`.`obj_id` ASC;
}}}
Problems:
1) Query 1 is missing a join.
2) Query 2 is using apptest_base.id in the where clause instead of
apptest_child.obj_id. The results from the generated sql and expected sql
would be the same. However, indexes created on the apptest_child table to
optimize the query cannot be used hence leading to a slow query.
3) Query 3 has the same problem as query 2 in the where clause as well as
the order by clause. In addition to that, it also performs an extra join
on the apptest_child table which gives different results from the
generated sql and the expected sql statements.
I believe that this happens because of the reverse foreign key field
{{obj}}. When {{child__obj}} is used, the query generation logic does not
know that we are referencing the {{obj}} field on the Child table and not
the {{id}} field on the Base table. If we could explicitly specify
{{child__obj_id}}, it would help, but thats not an options with the
current database model api code.
--
Ticket URL: <http://code.djangoproject.com/ticket/14055>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
