Code

Opened 3 years ago

Closed 3 years ago

Last modified 3 years ago

#16172 closed Bug (invalid)

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
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Attachments (1)

qtest.zip (6.3 KB) - added by mpdaugherty 3 years ago.
The smallest possible complete Django project that includes unit tests to diagnose this problem. Just unzip and run 'python manage.py test'

Download all attachments as: .zip

Change History (3)

Changed 3 years ago by mpdaugherty

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

comment:1 Changed 3 years ago by kmtracey

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Resolution set to invalid
  • Status changed from new to closed

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.)

comment:2 Changed 3 years ago by mpdaugherty

  • UI/UX unset

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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.