[RUBY-ON-RAILS] 가입 집합의 모든 값을 포함해야하지만, 더 포함 할 수 SQL

가입 집합의 모든 값을 포함해야하지만, 더 포함 할 수 SQL

나는 세 개의 테이블 제공, 스포츠를하고 테이블 offers_sports 가입 할 수 있습니다.

class Offer < ActiveRecord::Base
  has_and_belongs_to_many :sports
end

class Sport < ActiveRecord::Base
  has_and_belongs_to_many :offers
end

나는 스포츠 이름의 지정된 배열을 포함하는 이벤트를 선택합니다. 그들은 스포츠의 모든 메시지 있어야하지만 더있을 수 있습니다.

나는이 세 가지 이벤트가 있다고 가정하자 :

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

배열을 감안할 [ "바디 보딩을", "서핑"] 나는 중간을 제외한 모든 불이 들어오지을 얻고 싶은 것입니다.

나는이 답변의 라인을 따라 뭔가를 시도했지만 나는 결과 제로 행을 얻을 :

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

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;

액티브의 대답은 좋은 것입니다하지만 난 바람직하게 호환 POSTGRES, 단지 SQL 정착 것입니다.

데이터:

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

해결법

1. ==============================

   1.offer.id에 의해 그룹이 아닌 sports.name (또는 sports.id)에 의해 :

   offer.id에 의해 그룹이 아닌 sports.name (또는 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;
   

   전형적인 구현을 가정 :

   당신은 카운트 DISTINCT 필요가 없습니다. 그리고 카운트 (*) 아직, 심지어 조금 저렴합니다.

   가능한 기술의 무기고와 관련 대답 :

   @max (영업)에 의해 추가됨 -이 액티브로 압연 위의 쿼리는 다음과 같습니다

   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
   

2. ==============================

   2.그것을 할 수있는 한 가지 방법은 배열과 array_agg 집계 함수를 사용합니다.

   그것을 할 수있는 한 가지 방법은 배열과 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[];
   

   보고:

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

   에서 @> 왼쪽에 배열 오른쪽에있는 모든 요소를 ​​포함해야하지만, 더 포함될 수 연산자 수단. spnames 열은 단지 쇼를 위해,하지만 당신은 안전하게 제거 할 수 있습니다.

   이과의 매우 염두해야 두 가지가 있습니다.

from https://stackoverflow.com/questions/36131803/sql-where-joined-set-must-contain-all-values-but-may-contain-more by cc-by-sa and MIT license