Opened 4 years ago

Closed 4 years ago

Last modified 3 years ago

#14055 closed (duplicate)

Wrong query generated when using reverse foreign key

Reported by: premalshah Owned by: nobody
Component: Database layer (models, ORM) Version: 0.96
Severity: Keywords: foreign key
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:


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'''

''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;

1) Query 1 is missing a join.
2) Query 2 is using 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 {{childobj}} 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 {{childobj_id}}, it would help, but thats not an options with the current database model api code.

Attachments (0)

Change History (2)

comment:1 Changed 4 years ago by premalshah

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to duplicate
  • Status changed from new to closed

comment:2 Changed 3 years ago by jacob

  • milestone 1.3 deleted

Milestone 1.3 deleted

Add Comment

Modify Ticket

Change Properties
<Author field>
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'

E-mail address and user name can be saved in the Preferences.

Note: See TracTickets for help on using tickets.