我有三张桌子offers
, sports
和连接表offers_sports
.
class Offer < ActiveRecord::Base
has_and_belongs_to_many :sports
end
class Sport < ActiveRecord::Base
has_and_belongs_to_many :offers
end
我想选择的优惠include给定的运动名称数组。他们must包含所有的sports
but may有更多的。
假设我有这三个报价:
light:
- "Yoga"
- "Bodyboarding"
medium:
- "Yoga"
- "Bodyboarding"
- "Surfing"
all:
- "Yoga"
- "Bodyboarding"
- "Surfing"
- "Parasailing"
- "Skydiving"
给定数组["Bodyboarding", "Surfing"]
我想要得到medium
and all
但不是light
.
我尝试过类似的事情这个答案 https://stackoverflow.com/a/23721658/544825但我在结果中得到零行:
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;
ActiveRecord 答案会很好,但我会选择 SQL,最好是与 Postgres 兼容。
Data:
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