Comment sélectionner des offres contenant l'ensemble d'un ensemble de sports donné (mais peut-être plus) en SQL ?

SQL : WHERE L'ensemble joint doit contenir toutes les valeurs mais peut en contenir davantage

En SQL, la clause "WHERE JOINED SET" garantit qu'un la table jointe inclut des valeurs spécifiques dans son jeu de résultats. Toutefois, la table jointe peut également inclure des valeurs supplémentaires qui ne font pas partie de la condition. Ce concept peut être difficile à mettre en œuvre dans la pratique, en particulier lorsqu'il s'agit de déterminer les valeurs à inclure dans la condition.

Considérez l'exemple suivant :

Scénario :

Vous disposez de trois tableaux : offres, sports et offres_sports, qui représentent les offres, les sports et la relation entre eux. Vous souhaitez sélectionner des offres incluant un éventail donné de noms de sports. Les offres doivent contenir tous les sports mais peuvent également inclure des sports supplémentaires.

Données :

offers
| id | name |
| --- | ---- |
| 1 | light |
| 2 | medium |
| 3 | all |
| 4 | extreme |

sports
| id | name |
| --- | ---- |
| 1 | Yoga |
| 2 | Bodyboarding |
| 3 | Surfing |
| 4 | Parasailing |
| 5 | Skydiving |

offers_sports
| offer_id | sport_id |
| --- | ---- |
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
| 3 | 5 |
| 4 | 3 |
| 4 | 4 |
| 4 | 5 |

Résultat souhaité :

Étant donné le tableau ["Bodyboarding", "Surfing"], la requête doit renvoyer le support d'offres et tout car ils contiennent les deux éléments spécifiés le sport. L'offre légère ne doit pas être renvoyée car elle n'inclut pas les deux sports.

Requête incorrecte :

La requête suivante, qui regroupe par nom de sport et garantit qu'exactement deux les sports sont inclus dans chaque offre, ne renvoie aucun résultats :

SELECT "offers".* 
FROM "offers" 
INNER JOIN "offers_sports" ON "offers_sports"."offer_id" = "offers"."id"     
INNER JOIN "sports" ON "sports"."id" = "offers_sports"."sport_id" 
  WHERE "sports"."name" IN ('Bodyboarding', 'Surfing') 
GROUP BY sports.name 
HAVING COUNT(distinct sports.name) = 2;

Solution :

La requête correcte regroupe par identifiant d'offre au lieu du nom du sport et vérifie le nombre de sports distincts inclus dans chaque offre à l'aide de COUNT( DISTINCT ):

SELECT o.*
FROM   sports        s
JOIN   offers_sports os ON os.sport_id = s.id
JOIN   offers        o  ON os.offer_id = o.id
WHERE  s.name IN ('Bodyboarding', 'Surfing') 
GROUP  BY o.id  -- !!
HAVING count(*) = 2;

Cette requête renverra le support d'offres et tout car ils incluent tous les deux les éléments spécifiés sports.

Mise en œuvre d'ActiveRecord :

class Offer < ActiveRecord::Base
  has_and_belongs_to_many :sports
  def self.includes_sports(*sport_names)
    joins(:sports)
      .where(sports: { name: sport_names })
      .group('offers.id')
      .having("COUNT(DISTINCT sports.name) = ?", sport_names.size)
  end
end

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!