[Django]-Trouble with chaining Q() objects with the same argument in Django's ORM

by

2👍

✅

The difference between the two approaches to filter() is described in Spanning multi-valued relationships:

Everything inside a single filter() call is applied simultaneously to filter out items matching all those requirements…. For multi-valued relations, they apply to any object linked to the primary model, not necessarily those objects that were selected by an earlier filter() call.

The example in the documentation makes it more clear. I’ll rewrite it in terms of your problem:

To select all recipes that contain an ingredient with both type 3 and type 7, we would write:

Recipe.objects.filter(ingredients__ingredient_type=3, ingredients__ingredient_type=7)

That is of course impossible in your model, so this would return an empty queryset, just like your Q example with AND.

To select all recipes that contain an ingredient with type 3 as well as an ingredient with type 7, we would write:

Recipe.objects.filter(ingredients__ingredient_type=3).filter(ingredients__ingredient_type=7)

It’s not especially intuitive, but they needed a way to distinguish these two cases and this is what they came up with.

Back to your problem, the OR case can be made simpler by using the in operator:

Recipe.objects.filter(ingredients__ingredient_type__in=[3, 7]).distinct()

The AND case is complicated because it’s a condition that involves multiple rows. A simple approach would be to just take the OR version above and further process it in Python to find the subset that has all the ingredients.

A query approach that should work involves annotation with Count. This is untested, but something like:

Recipe.objects.annotate(num_ingredients=Count("ingredients", 
                            filter=Q(ingredients__ingredient_type__in=[3, 7]))
              .filter(num_ingredients=2)
👤Kevin Christopher Henry

1👍

Another approach to the AND case for Django 1.11+ would be to use the relatively new QuerySet intersection() method. As per the docs, this method:

Uses SQL’s INTERSECT operator to return the shared elements of two or more QuerySets.

So given an arbitrary list of IngredientType primary keys, you could create a filter() query for each pk (let’s call these subqueries) and then spread that list (the * operator) into the intersection() method.

Like so:

# the base `QuerySet` and `IngredientType` pks to filter on
queryset = Recipe.objects.all()
ingredient_type_pks = [3, 7]

# build the list of subqueries
subqueries = []
for pk in ingredient_type_pks:
    subqueries.append(queryset.filter(ingredients__ingredient_type__pk=pk))

# spread the subqueries into the `intersection` method
return queryset.intersection(*subqueries).distinct()

I added distinct() in there just to be safe and avoid duplicate results, but I am actually not certain whether it’s necessary. Will have to test and update this post later.

👤whusterj

Leave a comment