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 Rezept(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_ingredient=selected_count_ingredient" % 
            ",".join([str(ingredient.id) for ingredient in ingredients])
rezepte = Rezept.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.