4
votes

Comment enchaîner des multiples IN AND on WHERE en SQL?

Je veux faire quelque chose comme:

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

Mais cette requête renvoie 0 lignes ...
Je sais que cette demande ne peut pas fonctionner, mais je ne trouve pas de solution

Je veux obtenir des "Recettes" contenant des ingrédients 5 OU 6 AND code > 10 OU 11. Pensez comme:

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

Je veux des "Recettes" avec des tomates OU grosses tomates ET pommes de terre OU code> grosses pommes de terre.

Solution adoptée

Parce que les ingrédients peuvent être aléatoires, j'ai écrit une portée:

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

postgresql sql ruby-on-rails

4 commentaires

La balise ruby-on-rails est-elle pertinente ou vous avez besoin d'une solution SQL simple?

Soit GROUP BY, soit auto-jointure.

J'ai mis l'étiquette ruby-on-rails car s'il y a une solution en ruby-on-rails c'est mieux mais je ne pense pas ...

@ mjerem34 soyez prudent avec cela car la concaténation de chaînes peut être potentiellement dangereuse selon la provenance des données. J'ai adapté ma réponse pour implémenter la fonctionnalité souhaitée en utilisant arel

3 Réponses :

0
votes

utilisez GROUP BY et HAVING:

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"
 GROUP BY "recipes"."id"
 HAVING COUNT(CASE WHEN ingredients.id IN (5, 6) THEN 1 END) > 0 
    AND COUNT(CASE WHEN ingredients.id IN (10, 11) THEN 1 END) > 0

0 commentaires

2
votes

Vous voulez une agrégation. Regroupez par recette et voyez si elle contient les ingrédients désirés.

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));

2 commentaires

Vous pouvez traduire ce qui précède avec ActiveRecord en: Recipe.joins (groups: {steps: {steps_ingredients_memberships:: Ingrédients) .group ('recette.id'). )) ET BOOL_OR (i.id IN (10, 11)) ')

Merci @MrYoshiji, j'ai utilisé votre solution! Je mettrai à jour mon message pour écrire la solution que j'ai utilisée

1
votes

Pour en savoir plus sur le commentaire de @ MrYoshiji Ici nous pouvons rendre cela plus" railsy "comme suit:

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

Cela produira du SQL similaire à la réponse liée: (fourni par @ThorstenKettner ) par exemple

XXX

Cependant, il offre la flexibilité de changer Ingredient_list1 et Ingredient_list2 d'une manière beaucoup plus efficace sans souci de fuite et autres.

BTW pour répondre à votre commentaire:

"J'ai mis la balise ruby-on-rails car s'il y a une solution dans ruby-on-rails c'est mieux mais je ne pense pas ..."

arel peut assembler n'importe quelle requête que vous pouvez imaginer (il peut également assembler du SQL invalide / fragmenté). S'il est valide, SQL ActiveRecord peut l'exécuter, donc si vous utilisez des rails et que vous avez des questions de requête, incluez définitivement le message ruby-on-rails comme vous l'avez fait.

Mettre à jour strong > (basé sur votre solution publiée) mais en utilisant arel plutôt que la concaténation de chaînes

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

2 commentaires

puisqu'il s'agit de Postgres, serait-il judicieux d'agréger la liste des ingrédients dans un tableau, puis d'utiliser des fonctions de tableau pour vérifier la présence d'ingrédients?

@GarrettMotzner pour être honnête, je ne connais pas très bien Postgres et ses fonctions. Je connais très bien la façon dont arel gère l'assemblage de requêtes et sa capacité croisée avec les requêtes de rails natives. Si vous pouvez publier la syntaxe de l'agrégation, je peux certainement la convertir en arel