sql - 如何在 SQL 中链接多个 IN AND on WHERE？

Question

我想做这样的事情:

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 OR 6 AND 10 OR 11 的“食谱”。像这样想:

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

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

采用的解决方案

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

  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 '))
  }

谢谢大家! :)

Answer 1

展开@MrYoshiji's评论 Here我们可以使它更“railsy”如下:

ingredients_table = Ingredient.arel_table
ingredient_list1 = [5,6]
ingredient_list2 = [10,11]
condition = Arel::Nodes::NamedFunction.new(
              'BOOL_OR',[ingredients_table[:id].in(ingredient_list1)]
            ).and(
              Arel::Nodes::NamedFunction.new(
                'BOOL_OR',[ingredients_table[:id].in(ingredient_list2)]
              )
            )
recipes = Recipe.joins(groups: { 
                       steps: { 
                         steps_ingredients_memberships: :ingredients
                       }
                     })
        .group('recipes.id')
        .having(condition)

这将生成类似于链接答案的 SQL:(由 @ThorstenKettner 提供)例如

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

然而，它提供了以更有效的方式更改 ingredient_list1 和 ingredient_list2 的灵 active ，而无需担心转义等问题。

顺便说一句，解决您的评论:

"I put the ruby-on-rails tag because if there is a solution in ruby-on-rails it's better but I don't think so ..."

arel 可以组合您能想到的任何查询(它也可以组合无效/碎片化的 SQL)。如果它是有效的 SQL ActiveRecord 可以运行它，所以如果您使用的是 Rails 并且有查询问题肯定包括 ruby-on-rails像您一样标记。

更新(基于您发布的解决方案)但使用 arel 而不是字符串连接

scope :ingredients_filtering, lambda { |ingredients|
  return unless ingredients
  ingredients_table = Ingredient.arel_table
  conditions = ingredients.map do |ingredient|
    Arel::Nodes::NamedFunction.new(
          'BOOL_OR',[ingredients_table[:id].in(ingredient.related_ids)]
        )
  end.reduce(&:and)
  group(:id).having(conditions)
}