tickets: 8254

This data as json

id created changetime last_pulled_from_trac stage status component type severity version resolution summary description owner reporter keywords easy has_patch needs_better_patch needs_tests needs_docs ui_ux
8254 2008-08-12 11:17:59 2011-09-28 16:12:17 2022-03-06 03:41:46.095182 Accepted closed Core (Other)     dev fixed incorrect sql query in many to many backwards relation using an intermediate table 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`) mtredinnick ido m2m many2many ManyToMany sql query through 0 0 0 0 0 0