Select those objects whose related objects IDs are *all* in given string

Question

Hi Django people,

I want to build a frontend to a recipe database which enables the user to search for a list of recipes which are cookable with the ingredients the user supplies.

I have the following models

class Ingredient(models.Model):
    name = models.CharField(max_length=100, unique=True)
    slug = models.SlugField(max_length=100, unique=True)
    importancy = models.PositiveSmallIntegerField(default=4)
    […]

class Amount(models.Model):
    recipe = models.ForeignKey('Recipe')
    ingredient = models.ForeignKey(Ingredient)
    […]

class Recipe(models.Model):
    name = models.CharField(max_length=100)
    slug = models.SlugField()
    instructions = models.TextField()
    ingredients = models.ManyToManyField(Ingredient, through=Amount)
    […]

and a rawquery which does exactly what I want: It gets all the recipes whose required ingredients are all contained in the list of strings that the user supplies. If he supplies more than necessary, it's fine too.

query = "SELECT *, 
    COUNT(amount.zutat_id) AS selected_count_ingredients, 
    (SELECT COUNT(*) 
            FROM amount 
            WHERE amount.recipe_id = amount.id) 
    AS count_ingredients 
    FROM amount LEFT OUTER JOIN amount 
    ON (recipe.id = recipe.recipe_id) 
    WHERE amount.ingredient_id IN (%s) 
    GROUP BY amount.id 
    HAVING count_ingredients=selected_count_ingredients" % 
            ",".join([str(ingredient.id) for ingredient in ingredients])
recipes = Recipe.objects.raw(query)

Now, what I'm looking for is a way that does not rely on .raw() as I would like to do it purely with Django's queryset methods.

Additionally, it would be awesome if you guys knew a way of including the ingredient's importancy in the lookup so that a recipe is still shown as a result even though one of its ingredients (that has an importancy of 0) is not supplied by the user.

Answer 1

It looks like you did a pretty good job of setting up the models for this. You could do it in two very simple queries, which will probably run as fast as a single query with a join.

amounts = Amount.objects.filter(ingredient__in=ingredients)
rezepte = Rezept.objects.filter(
        pk__in=amounts.values_list('recipe', flat=True)
    ).order_by('importancy')

You can even save memory by putting it into one statement, if you prefer:

rezepte = Rezept.objects.filter(
        pk__in=Amount.objects.filter(
                ingredient__in=ingredients
        ).values_list('recipe', flat=True)
    ).order_by('importancy')

Both of the queries should automagically get an index by default out of django. You'll end up with two pretty clean queries:

SELECT `yourproject_amount`.`recipe_id` FROM `yourproject_amount`;
SELECT * FROM `yourproject_rezept`
        WHERE `yourproject_rezept`.`id` IN (1, 2, 3, 4)
        ORDER BY `yourproject_rezept`.`importancy`;

Answer 2

You can use the possibillity to concat a queryset.

Simplest way (but not very efficient, because of joining the Amount table more than once in sql...):

ingredients = Ingredient.objects.filter(...)
query = Recipe.object.all()
for i in ingredients:
    query = query.filter(ingredients=i)


print query # at this database query will be exceuted