mysql - 首先在 mysql 中选择最匹配的行

Question

我的项目在 laravel 5.4，mysql 5.7 上运行

我有4张 table

recipes             (id, name)
ingredient_recipe   (id, recipe_id, ingredient_id, amount)
ingredients         (id, name, cat_id)
ingredient_category (id, name)

recipes 和 ingredients 通过 ingredient_recipe 表具有多对多关系。每个食谱都可以有很多成分。每种成分都有其类别 cat_id，它引用 ingredient_category 表中的 id。

我需要选择成分类别 ID 等于请求值的所有食谱，并将最匹配的值放在顶部。例如，请求的成分类别 ID 是 [23,56,76,102,11]。假设食谱 foo 包含类别匹配 23,56,76 的成分，bar 匹配 23,56 和 baz 匹配 23。它们应该被排序 - foo, bar, baz。我怎样才能以这种方式订购它们？

这是我的sql代码

 --select recipes
SELECT * from recipes where exists
    --select recipe's ingredients
   (select `ingredients`.`id` 
    from `ingredients` 
    inner join 
       `ingredient_recipe` on `ingredients`.`id` = 
       `ingredient_recipe`.`ingredient_id` where `recipes`.`id` = 
       `ingredient_recipe`.`recipe_id` 
    and exists 
      --select ingredient categories, where id .. 
     (select `ingredient_category`.`id` 
      from `ingredient_category` 
      where `ingredients`.`cat_id` = `ingredient_category`.`id` 
      and `id` IN (23,56,76,102,11)))

但是这段代码并没有将大多数匹配的食谱“放在”顶部。我知道我可以像这个例子一样选择然后过滤它们，但是有没有办法在 sql 中做？

Answer 1

通过联结表ingredient_recipe将recipe表连接到ingredient表，然后按recipe聚合。对于每个食谱，我们可以计算与您的列表对应的配料数量，然后我们可以对结果集进行排序，匹配度高的排在最前面。

SELECT
    r.id,
    r.name,
    COUNT(CASE WHEN i.cat_id IN (23, 56, 76, 102, 11) THEN 1 END) AS match_cnt 
FROM recipes r
INNER JOIN ingredient_recipe ir
    ON r.id = ir.recipe_id
INNER JOIN ingredients i
    ON ir.ingredient_id = i.id
GROUP BY r.id
ORDER BY match_cnt DESC;

我们还可以添加一个 HAVING 子句，例如过滤掉不符合最低匹配成分数量的食谱。我们还可以使用 LIMIT 子句来限制匹配总数。