SQL wherejoined set 必须包含所有值,但可以包含更多 [英] SQL where joined set must contain all values but may contain more
问题描述
我有三个表offers
、sports
和连接表offers_sports
.
I have three tables offers
, sports
and the join table offers_sports
.
class Offer < ActiveRecord::Base
has_and_belongs_to_many :sports
end
class Sport < ActiveRecord::Base
has_and_belongs_to_many :offers
end
我想选择包括一系列给定的运动名称的优惠.它们必须包含所有sports
,但可能有更多.
I want to select offers that include a given array of sport names. They must contain all of the sports
but may have more.
假设我有这三个优惠:
light:
- "Yoga"
- "Bodyboarding"
medium:
- "Yoga"
- "Bodyboarding"
- "Surfing"
all:
- "Yoga"
- "Bodyboarding"
- "Surfing"
- "Parasailing"
- "Skydiving"
给定数组 ["Bodyboarding", "Surfing"]
我想要 medium
和 all
但不是 light
.
Given the array ["Bodyboarding", "Surfing"]
I would want to get medium
and all
but not light
.
我已经尝试了类似这个答案的方法,但结果中的行数为零:
I have tried something along the lines of this answer but I get zero rows in the result:
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 兼容.
An ActiveRecord answer would be nice but I'll settle for just SQL, preferably Postgres compatible.
数据:
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
推荐答案
Group by offer.id
,而不是 sports.name
(或 sports.id
):
Group by offer.id
, not by sports.name
(or 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;
假设典型实现:
offer.id
和sports.id
被定义为主键.sports.name
被定义为唯一的.(sport_id, offer_id)
在offers_sports
中被定义为唯一的(或 PK).
offer.id
andsports.id
are defined as primary key.sports.name
is defined unique.(sport_id, offer_id)
inoffers_sports
is defined unique (or PK).
计数中不需要 DISTINCT
.而且 count(*)
甚至更便宜一些.
You don't need DISTINCT
in the count. And count(*)
is even a bit cheaper, yet.
与一系列可能的技术相关的答案:
Related answer with an arsenal of possible techniques:
由@max(OP)添加 - 这是上面的查询滚动到 ActiveRecord 中:
Added by @max (the OP) - this is the above query rolled into 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
这篇关于SQL wherejoined set 必须包含所有值,但可以包含更多的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!