#25486: ORM generating incorrect query for through-model on
<RelatedManager>.all()
-------------------------------------+-------------------------------------
Reporter: pembo13 | Owner: nobody
Type: Bug | Status: closed
Component: Database layer | Version: 1.8
(models, ORM) |
Severity: Normal | Resolution: duplicate
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by timgraham):
* status: new => closed
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
* resolution: => duplicate
Old description:
> I have a through-model which itself has a through model (because it needs
> a many-to-many relationship). The problem comes when i try to get related
> mode through this many-to-many relationship. The ORM generates the WHERE
> clause on the wrong field (the pk, instead of the fk).
>
> {{{
> #!sql
> -- generated sql
> SELECT "core_role"."id", "core_role"."name"
> FROM "core_role"
> INNER JOIN "core_dealercontactrole" ON ( "core_role"."id" =
> "core_dealercontactrole"."role_id" )
> WHERE "core_dealercontactrole"."id" = 8743
> ORDER BY "core_role"."name" ASC
>
> -- corrected sql
> SELECT "core_role"."id", "core_role"."name"
> FROM "core_role"
> INNER JOIN "core_dealercontactrole" ON ( "core_role"."id" =
> "core_dealercontactrole"."role_id" )
> WHERE "core_dealercontactrole"."dealercontact_id" = 8743
> ORDER BY "core_role"."name" ASC
> }}}
>
> The code that generated the query:
>
> {{{
> #!python
> dc = DealerContact.objects.get(id=8743)
> >>> unicode(dc.roles.all().query)
> u'SELECT "core_role"."id", "core_role"."name" FROM "core_role" INNER JOIN
> "core_dealercontactrole" ON ( "core_role"."id" =
> "core_dealercontactrole"."role_id" ) WHERE "core_dealercontactrole"."id"
> = 8743 ORDER BY "core_role"."name" ASC'
> }}}
>
> Here are the models (with superfluous fields removed):
>
> {{{
> #!python
> class Contact(models.Model):
>
> first_name = models.CharField(blank=True, max_length=50)
> last_name = models.CharField(blank=True, max_length=50)
>
> class Meta:
> ordering = ('last_name','first_name')
>
> pass
>
> class Dealer(models.Model):
>
> contacts = models.ManyToManyField('Contact',
> through='DealerContact', related_name='dealers')
>
> name = models.CharField(db_index=True, max_length=250)
>
> pass
>
> class DealerContact(models.Model):
>
> dealer = models.ForeignKey('Dealer',
> related_name='dealercontacts+', on_delete=models.CASCADE)
> contact = models.ForeignKey('Contact', related_name='+',
> on_delete=models.CASCADE)
> roles = models.ManyToManyField('Role',
> through='DealerContactRole', related_name='+')
>
> class Meta:
> unique_together = ('dealer','contact')
>
> pass
>
> class DealerContactRole(models.Model):
>
> dealercontact = models.ForeignKey('DealerContact',
> related_name='+', on_delete=models.CASCADE)
> role = models.ForeignKey('Role', related_name='+')
>
> class Meta:
> unique_together = ('dealercontact','role')
>
> pass
>
> class Role(models.Model):
>
> name = models.CharField(db_index=True, max_length=100)
>
> class Meta:
> ordering = ('name',)
>
> pass
> }}}
New description:
I have a through-model which itself has a through model (because it needs
a many-to-many relationship). The problem comes when i try to get related
mode through this many-to-many relationship. The ORM generates the WHERE
clause on the wrong field (the pk, instead of the fk).
{{{
#!sql
-- generated sql
SELECT "core_role"."id", "core_role"."name"
FROM "core_role"
INNER JOIN "core_dealercontactrole" ON ( "core_role"."id" =
"core_dealercontactrole"."role_id" )
WHERE "core_dealercontactrole"."id" = 8743
ORDER BY "core_role"."name" ASC
-- corrected sql
SELECT "core_role"."id", "core_role"."name"
FROM "core_role"
INNER JOIN "core_dealercontactrole" ON ( "core_role"."id" =
"core_dealercontactrole"."role_id" )
WHERE "core_dealercontactrole"."dealercontact_id" = 8743
ORDER BY "core_role"."name" ASC
}}}
The code that generated the query:
{{{
#!python
dc = DealerContact.objects.get(id=8743)
>>> unicode(dc.roles.all().query)
u'SELECT "core_role"."id", "core_role"."name" FROM "core_role" INNER JOIN
"core_dealercontactrole" ON ( "core_role"."id" =
"core_dealercontactrole"."role_id" ) WHERE "core_dealercontactrole"."id" =
8743 ORDER BY "core_role"."name" ASC'
}}}
Here are the models (with superfluous fields removed):
{{{
#!python
class Contact(models.Model):
first_name = models.CharField(blank=True, max_length=50)
last_name = models.CharField(blank=True, max_length=50)
class Meta:
ordering = ('last_name', 'first_name')
class Dealer(models.Model):
contacts = models.ManyToManyField('Contact', through='DealerContact',
related_name='dealers')
name = models.CharField(db_index=True, max_length=250)
class DealerContact(models.Model):
dealer = models.ForeignKey('Dealer', related_name='dealercontacts+',
on_delete=models.CASCADE)
contact = models.ForeignKey('Contact', related_name='+',
on_delete=models.CASCADE)
roles = models.ManyToManyField('Role', through='DealerContactRole',
related_name='+')
class Meta:
unique_together = ('dealer', 'contact')
class DealerContactRole(models.Model):
dealercontact = models.ForeignKey('DealerContact', related_name='+',
on_delete=models.CASCADE)
role = models.ForeignKey('Role', related_name='+')
class Meta:
unique_together = ('dealercontact', 'role')
class Role(models.Model):
name = models.CharField(db_index=True, max_length=100)
class Meta:
ordering = ('name',)
}}}
--
Comment:
This seems to be a regression in 1.8 and a duplicate of #24505 (bisected
to fb48eb05816b1ac87d58696cdfe48be18c901f16 and fixed subsequently fixed
in 4ee08958f154594b538207a53c1d457687b3f7ae). I'll backport that fix to
1.8.
--
Ticket URL: <https://code.djangoproject.com/ticket/25486#comment:1>
Django <https://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 unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/065.97b717465e2bcc1c2a392fe688f3c9fc%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
