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

Question

I am working on creating a cocktail recipe app as a learning exercise.

I am trying to create a filter through Django's Rest Framework that accepts a string of ingredient IDs through a query parameter (?=ingredients_exclusive=1,3,4), and then searches for all recipes that have all of those ingredients. I would like to search for “All cocktails that have both rum and grenadine” and then also, separately “All cocktails that have rum, and all cocktails that have grendaine.”

The three models in my app are Recipes, RecipeIngredients, and IngredientTypes. Recipes (Old Fashioned) have multiple RecipeIngredients (2oz of Whiskey), and RecipeIngredients are all of a Ingredient Type (Whiskey). I will eventually change the RecipeIngredient to a through model depending on how far I decide to take this.

The list can be of a variable length, so I cannot just chain together filter functions. I have to loop through the list of ids and then build a Q().

However, I'm having some issues. Through the Django Shell, I have done this:

>>> x = Recipe.objects.all()
>>> q = Q(ingredients__ingredient_type=3) & Q(ingredients__ingredient_type=7)
>>> x.filter(q)

>>> x.filter(ingredients__ingredient_type=3).filter(ingredients__ingredient_type=7)
]>

So here's my question: Why is the Q object that ANDs the two queries different than the chained filters of same object?

I've read through the "Complex lookups with Q objects" in the Django documentation and it doesn't seem to help.

Just for reference, here are my filters in Filters.py.

The "OR" version of this command is working properly:

class RecipeFilterSet(FilterSet):
    ingredients_inclusive = django_filters.CharFilter(method='filter_by_ingredients_inclusive')
    ingredients_exclusive = django_filters.CharFilter(method='filter_by_ingredients_exclusive')

    def filter_by_ingredients_inclusive(self, queryset, name, value):
        ingredients = value.split(',')
        q_object = Q()
        for ingredient in ingredients:
            q_object |= Q(ingredients__ingredient_type=ingredient)
        return queryset.filter(q_object).distinct()

    def filter_by_ingredients_exclusive(self, queryset, name, value):
        ingredients = value.split(',')
        q_object = Q()
        for ingredient in ingredients:
            q_object &= Q(ingredients__ingredient_type=ingredient)
        return queryset.filter(q_object).distinct()

    class Meta:
        model = Recipe
        fields = ()

I've also included my models below:

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models


class IngredientType(models.Model):
  name = models.CharField(max_length=256)

  CATEGORY_CHOICES = (
    ('LIQUOR', 'Liquor'),
    ('SYRUP', 'Syrup'),
    ('MIXER', 'Mixer'),
  )

  category = models.CharField(
    max_length=128, choices=CATEGORY_CHOICES, default='MIXER')

  def __str__(self):
    return self.name


class Recipe(models.Model):
  name = models.CharField(max_length=256)

  def __str__(self):
    return self.name


class RecipeIngredient(models.Model):
  ingredient_type = models.ForeignKey(IngredientType, on_delete=models.CASCADE, related_name="ingredients")
  quantity = models.IntegerField(default=0)
  quantity_type = models.CharField(max_length=256)
  recipe = models.ForeignKey(Recipe, on_delete=models.CASCADE, related_name="ingredients")

  @property
  def ingredient_type_name(self):
    return self.ingredient_type.name

  @property
  def ingredient_type_category(self):
    return self.ingredient_type.category

  def __str__(self):
    return f'{self.quantity}{self.quantity_type} of {self.ingredient_type}'

Any help would be very much appreciated!

user_2395796 · Answer

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)

user_2174390 · Answer

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.

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

2 Answers