Filtering queryset using Q objects to find objects with two specific values on the same many:many relationship returns 0 results

[mpdaugherty]

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.

[mpdaugherty]

The smallest possible complete Django project that includes unit tests to diagnose this problem. Just unzip and run 'python manage.py test'

[Karen Tracey]

I believe the Q objects are doing what they are intended to do, it's just not the right way to approach what you are looking for. I believe the query you want is:

A.objects.filter(tags__value__iexact='T1').filter(tags__value__iexact='T2')

(Changing the behavior the Q objects would be more than a performance drawback, it would change the results of the queries. There are use cases where their current behavior is what is needed, changing in the way you propose is not an option since that would break those valid use cases.)

[mpdaugherty]

Thanks, kmtracey. I just came back to write the same thing; after finding that the Django unit tests specifically check for the behavior I thought was a bug, I reread ​https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships in more detail and finally understood the design decision.

I'm working on a project that gives users the ability to create arbitrary queries through the data, so our initial approach was to create a UI for this and then serialize their queries into Q objects. However, it sounds like we'll have to do something more complex, because not every query can be expressed as a single .filter(Q) expression.