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)
<QuerySet []>
>>> x.filter(ingredients__ingredient_type=3).filter(ingredients__ingredient_type=7)
<QuerySet [<Recipe: Rum and Tonic>]>

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!

Kevin Christopher Henry · Accepted Answer · 2018-10-29 00:59:59Z

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)

edited Oct 29, 2018 at 0:59

answered Oct 28, 2018 at 3:54

Kevin Christopher Henry

49.7k8 gold badges125 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Neil Shah Over a year ago

This is great - and very helpful. That makes complete sense. What is the right way to filter by the list ids of variable length, then?

Kevin Christopher Henry Over a year ago

@NeilShah: Oh, right, I forgot about the actual problem you're trying to solve. See if my edit helps.

Neil Shah Over a year ago

Thanks so much! I will try it tonight. Really appreciate your help and your time.

whusterj · Accepted Answer · 2018-10-30 14:21:42Z

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.

answered Oct 30, 2018 at 14:21

whusterj

3,4291 gold badge19 silver badges14 bronze badges

1 Comment

Kevin Christopher Henry Over a year ago

Interesting, I hadn't realize they added that. distinct() shouldn't be necessary since Django is using INTERSECT, not INTERSECT ALL. (Though it's an option on union()).

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related