#12937 closed (fixed)
Select related() not working as of Beta
Reported by: | Yeago | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 1.2-beta |
Severity: | Keywords: | ||
Cc: | Triage Stage: | Accepted | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
Given the following expression:
bmodels.Contact.objects.select_related('IR_contact','consumer_contact','program','consumer').filter(consumer__file=file).order_by('-date')
And the following models:
class Contact(models.Model): program = models.ForeignKey('agency.Program') consumer = models.ForeignKey('consumer.Consumer',null=True,blank=True) date = models.DateField('Contact Date') notes = models.TextField() date_added = models.DateTimeField(editable=False) method = models.CharField(max_length=10,choices=choices.CONTACT_METHOD,\ null=True,blank=True) # consumer_contact class Contact(basemodels.Contact): base = models.OneToOneField(basemodels.Contact, parent_link=True,related_name="consumer_contact") service = models.ForeignKey(Service) confidential = models.BooleanField(default=False,help_text='These notes will not be visible to general staff') group = models.ForeignKey(Group,null=True,blank=True) group_contact = models.ForeignKey('consumer.GroupContact',blank=True,null=True) def save(self, *args, **kwargs): super(Contact, self).save(*args,**kwargs) class Meta: permissions = ( ("allow_confidential", "Can make contacts confidential"), ) #ir_contact class Contact(basemodels.Contact): base=models.OneToOneField(BaseContact,parent_link=True,related_name='IR_contact') IR=models.ForeignKey(IR,blank=True,null=True) referred_from=models.ForeignKey(ReferralSource,related_name='IR_referred_from') referred_to=models.ManyToManyField(ReferralSource,related_name='IR_referred_to') information=models.ManyToManyField(Information,verbose_name='Information Requested')
the following SQL is produced:
SELECT (fields) FROM `base_contact` LEFT OUTER JOIN `consumer_consumer` ON (`base_contact`.`consumer_id` = `consumer_consumer`.`id`) # ok INNER JOIN `agency_program` ON (`base_contact`.`program_id` = `agency_program`.`id`) # ok LEFT OUTER JOIN `consumer_contact` ON (`base_contact`.`id` = `consumer_contact`.`base_id`) # ok INNER JOIN `base_contact` T5 ON (`consumer_contact`.`base_id` = T5.`id`) # ?!? # These next two are questionable, since they are joined on a LEFT OUTER (so they may potentially not be there) INNER JOIN `consumer_service` ON (`consumer_contact`.`service_id` = `consumer_service`.`id`) INNER JOIN `consumer_servicetype` ON (`consumer_service`.`type_id` = `consumer_servicetype`.`id`) LEFT OUTER JOIN `IR_contact` ON (`base_contact`.`id` = `IR_contact`.`base_id`) # ok INNER JOIN `base_contact` T9 ON (`IR_contact`.`base_id` = T9.`id`) # ?!? WHERE `consumer_consumer`.`file` = 06-1757 ORDER BY `base_contact`.`date` DESC
As you can see, there are two INNER JOINs which are messing up this otherwise valid and expected query.
Change History (4)
comment:1 by , 15 years ago
Description: | modified (diff) |
---|
comment:2 by , 15 years ago
Triage Stage: | Unreviewed → Accepted |
---|
Ok - this is a weird case of an old bug colliding with a new one.
This problem doesn't exist in Django 1.1, because we didn't allow reverse select related lookups for OneToOneFields. However, we didn't prevent them either - as logged by #10414, listing non-existent (oor non-supported) fields in a select related doesn't actually raise an error. So in 1.1, the query looks something like:
SELECT (fields) FROM "myapp_basecontact" LEFT OUTER JOIN "myapp_consumer" ON ("myapp_basecontact"."consumer_id" = "myapp_consumer"."id") INNER JOIN "myapp_program" ON ("myapp_basecontact"."program_id" = "myapp_program"."id") WHERE "myapp_consumer"."file" = foo ORDER BY "myapp_basecontact"."date" DESC
Which is fine, but not as good as it could be.
In r12307, we added support for reverse select related fields. This means that the 'IR_contact' and 'consumer_contact' fields now get expanded.
However, because we are following a reverse one-to-one field, and that field forms an inherited link, the select_related is doing a forward select related back onto the parent model. This is what causes the T5 and T9 joins.
There are some tests for inheritance and reverse select related, so it's not just as simple as inheritance being the problem, but it's in that general ballpark.
There is a second problem hidden in here, which is the use of an INNER JOIN for the non-nullable field joined on a nullable link. I think this is a repeat of what is reported by #12819.
comment:3 by , 15 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
reformatted description, please use the Preview feature.