检查配方中是否包含成分-MYSQL [英] Checking that a recipe contains an ingredient - MYSQL

问题描述

大家好.我在高效运行查询/php组合时遇到了一些麻烦.我似乎只是在我的php的内部循环中循环了太多结果集.我敢肯定，有一种更有效的方法可以做到这一点.任何帮助都非常感谢.

Hey everyone. I'm having a bit of trouble running a query / php combination efficiently. I seem to be just looping over too many result sets in inner loops in my php. I'm sure there is a more efficient way of doing this. Any help very much appreciated.

我有一张可容纳3500条食谱的表格([食谱]):
rid | recipe_name

I've got a table that holds 3500 recipes ([recipe]):
rid | recipe_name

另一张桌子可容纳600种不同的成分([成分])
iid | i_name

And another table that holds 600 different ingredients ([ingredients])
iid | i_name

每个配方都有x个与之关联的成分，我使用一个不错的联接表来创建关联([recipe_ingredients])
uid | rid | iid
(其中uid只是表的唯一ID)

Each recipe has x number of ingredients associated to it, and I use a nice joining table to create the association ([recipe_ingredients])
uid | rid | iid
(where uid is just a unique id for the table)

例如:

rid: 1 | recipe_name: Lemon Tart
.....
iid: 99 | i_name: lemon curd
iid: 154 | i_name: flour
.....
1 | 1 | 99
2 | 1 | 154

我正在尝试运行的查询，它允许用户输入他们拥有的成分，并且它将告诉您使用这些成分可以做的任何事情.并不一定要使用所有成分，但是您确实需要使用食谱中的所有成分.

The query I'm trying to run, allows the user to enter what ingredients they have, and it will tell you anything you can make with those ingredients. It doesn;t have to use all ingredients, but you do need to have all the ingredients for the recipe.

例如，如果我有面粉，鸡蛋，盐，牛奶和柠檬酱，我可以制作煎饼"和柠檬T"(如果我们假设柠檬t没有其他成分:))，但不能制作意大利调味饭"(因为我没有饭，或者饭中需要的其他东西).

For instance if I had flour, egg, salt, milk and lemon curd I could make 'Pancakes', and 'Lemon Tart' (if we assume lemon tart has no other ingredients:)), but couldn't make 'Risotto' (as I didnt have any rice, or anything else thats needed in it).

在我的PHP中，我有一个包含用户拥有的所有成分的数组.目前，他们正在按照我的方式进行操作(正在处理每个配方)(循环1)，然后检查该配方中的所有成分，以查看每种成分是否包含在我的成分数组中(循环2).一旦找到食谱中没有的成分(我的阵列中没有)，它就会说否"并转到下一个食谱.如果是这样，它将rid存储在一个新数组中，我以后将使用它来显示结果.

In my PHP I have an array containing all the ingredients the user has. At the moment they way I'm running this is going through every recipe (loop 1) and then checking all ingredients in that recipe to see if each ingredient is contained in my ingredients array (loop 2). As soon as it finds an ingredient in the recipe, that isnt in my array, it says "no" and goes onto the next recipe. If it does, it stores the rid in a new array, that I use later to display the results.

但是，如果我们看一下它的效率，如果我假设有3500种配方，而我在阵列中有40种配料，则最坏的情况是它的运行时间为3500 x 40n，其中n =配方中的配料数.最好的情况仍然是3500 x 40(没有为每种食谱都第一次找到一种配料，因此退出了.)

But if we look at the efficiency of that, if I assume 3500 recipes, and Ive got 40 ingredients in my array, the worst case scenario is it running through 3500 x 40n, where n = number of ingredients in the recipe. The best case is still 3500 x 40 (doesn't find an ingredient first time for every recipe so exits).

我认为我的整个方法是错误的，而且我认为这里肯定缺少一些聪明的sql.有什么想法吗?我总是可以从我拥有的成分数组中建立一个sql语句...

I think my whole approach to this is wrong, and I think there must be some clever sql that I'm missing here. Any thoughts? I can always build up an sql statement from the ingredient array I have ......

非常感谢，非常感谢

推荐答案

我建议将配方中配料的数量存储在配方表中，只是为了提高效率(如果查询的话，查询会更快不必每次都计算此信息).这是非规范化，这对数据完整性不利，但对性能有利.您应该意识到，如果更新配方，这可能会导致数据不一致，并且您不小心确保在每个相关位置都更新了该编号.我以为您已经在配方表中将新列设置为ing_count来完成此操作.

I'd suggest storing the count of the number of ingredients for the recipe in the recipe table, just for efficiency's sake (it will make the query quicker if it doesn't have to calculate this information every time). This is denormalization, which is bad for data integrity but good for performance. You should be aware that this can cause data inconsistencies if recipes are updated and you are not careful to make sure the number is updated in every relevant place. I've assumed you've done this with the new column set as ing_count in the recipe table.

如果NAME1，NAME2等的值是通过用户输入提供的，请确保对它们进行转义-否则您就有可能进行SQL注入.

Make sure you escape the values in for NAME1, NAME2, etc if they are provided via user input - otherwise you are at risk for SQL injection.

select recipe.rid, recipe.recipe_name, recipe.ing_count, count(ri) as ing_match_count
from recipe_ingredients ri 
inner join (select iid from ingredients where i.name='NAME1' or i.name='NAME2' or i.NAME='NAME3') ing
on ri.iid = ing.iid
inner join recipe 
on recipe.rid = ri.rid
group by recipe.rid, recipe.recipe_name, recipe.ing_count
having ing_match_count = recipe.ing_count

如果您不想存储配方数，则可以执行以下操作:

If you don't want to store the recipe count, you could do something like this:

select recipe.rid, recipe.recipe_name, count(*) as ing_count, count(ing.iid) as ing_match_count
from recipe_ingredients ri 
inner join (select iid from ingredients where i.name='NAME1' or i.name='NAME2' or i.NAME='NAME3') ing
on ri.iid = ing.iid
right outer join recipe 
on recipe.rid = ri.rid
group by recipe.rid, recipe.recipe_name
having ing_match_count = ing_count

这篇关于检查配方中是否包含成分-MYSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！