id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
25486	ORM generating incorrect query for through-model on <RelatedManager>.all()	Arthur Pemberton	nobody	"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
}}}"	Bug	new	Database layer (models, ORM)	1.8	Normal				Unreviewed	0	0	0	0	0	0