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
8254	incorrect sql query in many to many backwards relation using an intermediate table	ido	Malcolm Tredinnick	"using the following models:
{{{
#!python
class Project(models.Model):
    name       = models.CharField(max_length = 30, primary_key = True)
    startDate  = models.DateField(auto_now_add = True)
    active     = models.BooleanField(default = True)

    def __unicode__(self):
        return self.name

class Tag(models.Model):
    id      = models.AutoField(primary_key = True)
    name    = models.CharField(max_length = 100)
    type    = models.CharField(max_length = 100)
    project = models.ForeignKey(Project)

    class Meta:
        unique_together = ('name', 'project')

    def __unicode__(self):
        return self.name

class Document(models.Model):
    text      = models.CharField(max_length=200)
    project   = models.ForeignKey(Project)
    tags      = models.ManyToManyField(Tag, through='Document2Tag')

    class Meta:
        unique_together = (""text"", ""project"")

    def __unicode__(self):
        return unicode(self.id)

class Document2Tag(models.Model):
    document  = models.ForeignKey(Document)
    tag     = models.ForeignKey(Tag)
    user    = models.ForeignKey(User)

    def __unicode__(self):
        return u'(%s, %s, %s)' % (self.document.id, self.tag.name, self.user.username)

}}}

I've made the following query:

{{{
#!python
>>> from general.models import *
>>> from django.contrib.auth.models import User
>>> user1 = User.objects.get(username='user1')
>>> user2 = User.objects.get(username='user2')
>>> project = Project.objects.get(name='project1')
>>> Document.objects.all()
[<Document: 1>, <Document: 2>]
>>> Tag.objects.all()
[<Tag: tag1>, <Tag: tag2>]
>>> Document2Tag.objects.all()
[<Document2Tag: (1, tag2, user2)>, <Document2Tag: (1, tag1, user1)>]
>>> tag1 = Tag.objects.get(name='tag1')
>>> tag1.document_set.filter(document2tag__user=user1)
[<Document: 1>]
>>> tag1.document_set.filter(document2tag__user=user2)
[<Document: 1>]
}}}

as you can see in the above scenario, 'tag1' is incorrectly associated with 'document1' when querying about 'user2'

the expected result would be an empty list on the last query.

the sql query for reference:
{{{
#!sql
>>> print tag1.document_set.filter(document2tag__user=user2).query
SELECT `general_document`.`id`, `general_document`.`text`, `general_document`.`project_id` 
FROM `general_document` 
INNER JOIN `general_document2tag` 
    ON (`general_document`.`id` = `general_document2tag`.`document_id`) 
INNER JOIN `general_document2tag` T4 
    ON (`general_document`.`id` = T4.`document_id`) 
WHERE (`general_document2tag`.`tag_id` = BINARY 1  AND T4.`user_id` = BINARY 3 )
}}}

(note the double inner join with `general_document2tag`)"		closed	Core (Other)	dev		fixed	m2m many2many ManyToMany  sql query through		Accepted	0	0	0	0	0	0