Filtering queryset using Q objects to find objects with two specific values on the same many:many relationship returns 0 results
|Reported by:||mpdaugherty||Owned by:||nobody|
|Component:||Database layer (models, ORM)||Version:||1.3|
|Has patch:||no||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
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.