#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 [email protected].
To post to this group, send email to [email protected].
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.
