test - SQL donde el conjunto unido debe contener todos los valores pero puede contener más

interseccion base de datos

Question

Tengo tres mesas de offers , sports y la mesa de unión offers_sports .

class Offer < ActiveRecord::Base
  has_and_belongs_to_many :sports
end

class Sport < ActiveRecord::Base
  has_and_belongs_to_many :offers
end

Quiero seleccionar ofertas que incluyan una variedad de nombres deportivos. Deben contener todos los sports pero pueden tener más.

Digamos que tengo estas tres ofertas:

light:
  - "Yoga"
  - "Bodyboarding"
medium:
  - "Yoga"
  - "Bodyboarding"
  - "Surfing"
all:
  - "Yoga"
  - "Bodyboarding"
  - "Surfing"
  - "Parasailing"
  - "Skydiving"

Dado el conjunto ["Bodyboarding", "Surfing"] me gustaría obtener un nivel medium y all pero no light .

He intentado algo similar a esta respuesta pero obtengo cero filas en el resultado:

Offer.joins(:sports)
     .where(sports: { name: ["Bodyboarding", "Surfing"] })
     .group("sports.name")
     .having("COUNT(distinct sports.name) = 2")

Traducido a SQL:

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;

Una respuesta de ActiveRecord sería buena, pero me conformaré con solo SQL, preferiblemente compatible con Postgres.

Datos:

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

Answer 1

Agrupe por offer.id , no por sports.name (o sports.id ):

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;

Asumiendo la implementación típica:

• offer.id y sports.id se definen como clave principal.
• sports.name se define como único.
• (sport_id, offer_id) en offers_sports se define como único (o PK).

No necesita DISTINCT en el recuento. Y count(*) es incluso un poco más barato, todavía.

Respuesta relacionada con un arsenal de posibles técnicas:

• Cómo filtrar resultados de SQL en una relación has-through

Agregado por @max (el OP): esta es la consulta anterior incluida en 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(*) = ?", sport_names.size)
  end
end

Answer 2

Una forma de hacerlo es usar matrices y la función agregada array_agg .

SELECT "offers".*, array_agg("sports"."name") as spnames 
FROM "offers" 
INNER JOIN "offers_sports" ON "offers_sports"."offer_id" = "offers"."id"     
INNER JOIN "sports" ON "sports"."id" = "offers_sports"."sport_id" 
GROUP BY "offers"."id" HAVING array_agg("sports"."name")::text[] @> ARRAY[''Bodyboarding'',''Surfing'']::text[];

devoluciones:

 id |  name  |                      spnames                      
----+--------+---------------------------------------------------
  2 | medium | {Yoga,Bodyboarding,Surfing}
  3 | all    | {Yoga,Bodyboarding,Surfing,Parasailing,Skydiving}
(2 rows)

El operador @> significa que la matriz de la izquierda debe contener todos los elementos del de la derecha, pero puede contener más. La columna de spnames es solo para mostrar, pero puede eliminarla de forma segura.

Hay dos cosas que debes tener muy en cuenta con esto.

1. Incluso con Postgres 9.4 (aún no he probado el 9.5), la conversión de tipos para comparar matrices es descuidada y, a menudo, produce errores, y le dice que no puede encontrar una manera de convertirlos a valores comparables, como puede ver en el ejemplo I he lanzado manualmente ambos lados usando ::text[] .

2. No tengo idea de cuál es el nivel de soporte para los parámetros de la matriz Ruby, ni el marco RoR, por lo que puede terminar teniendo que escapar manualmente de las cadenas (si es ingresado por el usuario) y formar la matriz usando la sintaxis ARRAY[] .