Select those objects whose related objects IDs are *all* in given string
- by Jannis
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.
