时间戳

首页 > 解决方案 > 如何在 SQL 中的 WHERE 中和上链接多重？

sql - 如何在 SQL 中的 WHERE 中和上链接多重？

问题描述

我想做类似的事情：

SELECT  "recipes"."id"
FROM "recipes"
    INNER JOIN "groups" ON "groups"."recipe_id" = "recipes"."id"
    INNER JOIN "steps" ON "steps"."group_id" = "groups"."id"
    INNER JOIN "steps_ingredients_memberships" ON "steps_ingredients_memberships"."step_id" = "steps"."id"
    INNER JOIN "ingredients" ON "ingredients"."id" =  "steps_ingredients_memberships"."ingredient_id"
 WHERE (ingredients.id IN (5, 6) AND ingredients.id IN (10, 11))
 LIMIT 10

但是这个请求返回 0 行......
我知道这个请求不能工作，但我找不到解决方案

我想得到含有成分 5 OR6 AND10 OR11 的“食谱”。想想：

5 = tomatoes
6 = big tomatoes
10 = potatoes
11 = big potatoes

OR我想要西红柿大西红柿AND土豆大土豆的“食谱” OR。

采用的解决方案

因为成分可以是随机的，所以我写了一个范围：

  scope :ingredients_filtering, lambda { |ingredients|
    return if ingredients.nil?

    queries = []
    ingredients.each do |ingredient|
      related_ids = ingredient.related_ids.uniq.join(', ')
      queries << "BOOL_OR(ingredients.id IN (#{related_ids}))"
    end
    group(:id).having(queries.join(' AND '))
  }

谢谢大家！:)

标签： sqlruby-on-railspostgresql

解决方案

你想要聚合。按食谱分组，看看它是否有所需的成分。

SELECT r.id 
FROM recipes r 
JOIN groups g ON g.recipe_id = r.id 
JOIN steps s ON s.group_id = g.id 
JOIN steps_ingredients_memberships sim ON sim.step_id = s.id 
JOIN ingredients i ON i.id = sim.ingredient_id 
GROUP BY r.id
HAVING BOOL_OR(i.id IN (5, 6)) AND BOOL_OR(i.id IN (10, 11));

推荐阅读