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
16172	Filtering queryset using Q objects to find objects with two specific values on the same many:many relationship returns 0 results	mpdaugherty	nobody	"I've attached an example Django project with a unit test that demonstrated specifically what the problem is.  Here's a quick explanation, however:

I have two models with a many-to-many field defined on one of them:

{{{
class Tag(models.Model):
    value = models.CharField(max_length=100)

class A(models.Model):
    name = models.CharField(max_length=100)
    tags = models.ManyToManyField(Tag)
}}}

I define two tags (T1, T2) assign them both to model A1.

Then, I want to find all As that have both T1 and T2, so I create a Q object and filter on it:

{{{
>>> q1 = Q(tags__value__iexact='T1')
>>> q2 = Q(tags__value__iexact='T2')
>>> q = q1 & q2
>>> A.objects.filter(q)
[]
}}}

However, the result of the filter should contain A1 because it has been tagged with both T1 and T2.

----

I found where this bug comes from in django.db.models.sql in function add_q.  The Query creates a set of table aliases that can be reused during processing of the Q object, and tries to reuse the same table alias for both parts of the AND within the Q.  Unfortunately, in this case, the generated SQL (e.g. ""select .... where value='T1' and value='T2';"") will never return any rows because a single cell can't have two different values.  Instead, there should be a join at this point.

Preventing Query from building the list of table references to reuse therefore solves this problem.  However, I'm sure it has some performance drawbacks, so the solution should probably be more subtle than that.  I haven't had a chance to come up with a better patch, however."	Bug	closed	Database layer (models, ORM)	1.3	Normal	invalid			Unreviewed	0	0	0	0	0	0