选择相关对象 ID 为给定字符串中*全部*的那些对象
我想构建一个食谱数据库的前端,使用户能够搜索可使用用户提供的原料烹饪的食谱列表。
我有以下模型
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)
[…]
和一个原始查询,它完全符合我的要求:它获取所需成分全部包含在用户提供的字符串列表中的所有食谱。如果他提供的比需要的多,也没关系。
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)
现在,我正在寻找一种不依赖 .raw()
的方法,因为我想纯粹使用 Django 的查询集方法来完成它。
此外,如果你们知道一种在查找中包含成分重要性的方法,这样即使用户未提供其中一种成分(重要性为 0),配方仍然会显示为结果,那就太棒了。
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
看来您为此建立模型做得非常好。您可以通过两个非常简单的查询来完成此操作,它们的运行速度可能与带有联接的单个查询一样快。
如果您愿意,您甚至可以通过将其放入一条语句中来节省内存:默认情况下,
两个查询都应自动从 django 中获取索引。你最终会得到两个非常干净的查询:
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.
You can even save memory by putting it into one statement, if you prefer:
Both of the queries should automagically get an index by default out of django. You'll end up with two pretty clean queries:
您可以使用可能性来连接查询集。
最简单的方法(但不是很有效,因为在 sql 中多次加入 Amount 表...):
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...):