Django ORM. Filter many to many with AND clause

Question

With the following models:

class Item(models.Model):
    name = models.CharField(max_length=255)
    attributes = models.ManyToManyField(ItemAttribute)


class ItemAttribute(models.Model):
    attribute = models.CharField(max_length=255)
    string_value = models.CharField(max_length=255)
    int_value = models.IntegerField()

I also have an Item which has 2 attributes, 'color': 'red', and 'size': 3.

If I do any of these queries:

Item.objects.filter(attributes__string_value='red')
Item.objects.filter(attributes__int_value=3)

I will get Item returned, works as I expected.

However, if I try to do a multiple query, like:

Item.objects.filter(attributes__string_value='red', attributes__int_value=3)

All I want to do is an AND. This won't work either:

Item.objects.filter(Q(attributes__string_value='red') & Q(attributes__int_value=3))

The output is:

<QuerySet []>

Why? How can I build such a query that my Item is returned, because it has the attribute red and the attribute 3?

This might help https://stackoverflow.com/questions/769843/how-do-i-use-and-in-a-django-filter/770078#770078 — Chayemor, May 21 '18 at 10:32

Chayemor · Accepted Answer · 2018-05-21T11:40:56.537

If it's of any use, you can chain filter expressions in Django:

query = Item.objects.filter(attributes__string_value='red').filter(attributes__int_value=3')

From the DOCS:

This takes the initial QuerySet of all entries in the database, adds a filter, then an exclusion, then another filter. The final result is a QuerySet containing all entries with a headline that starts with “What”, that were published between January 30, 2005, and the current day.

To do it with .filter() but with dynamic arguments:

args = {
    '{0}__{1}'.format('attributes', 'string_value'): 'red',
    '{0}__{1}'.format('attributes', 'int_value'): 3
}

Product.objects.filter(**args)

You can also (if you need a mix of AND and OR) use Django's Q objects.

Keyword argument queries – in filter(), etc. – are “AND”ed together. If you need to execute more complex queries (for example, queries with OR statements), you can use Q objects.

A Q object (django.db.models.Q) is an object used to encapsulate a collection of keyword arguments. These keyword arguments are specified as in “Field lookups” above.

You would have something like this instead of having all the Q objects within that filter:

** import Q from django
from *models import Item

#assuming your arguments are kwargs
final_q_expression = Q(kwargs[1])
for arg in kwargs[2:..]
    final_q_expression = final_q_expression & Q(arg);
result = Item.objects.filter(final_q_expression)

This is code I haven't run, it's out of the top of my head. Treat it as pseudo-code if you will.

Although, this doesn't answer why the ways you've tried don't quite work. Maybe it has to do with the lookups that span relationships, and the tables that are getting joined to get those values. I would suggest printing yourQuerySet.query to visualize the raw SQL that is being formed and that might help guide you as to why .filter( Q() & Q()) is not working.

Django ORM. Filter many to many with AND clause

1 Answers1