连接集必须包含所有值但可能包含更多值的SQL [英] 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.

可以说我有以下三个提议:

Lets say I have these three offers:

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

推荐答案

按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被定义为唯一.
offers_sports中的
• (sport_id, offer_id)被定义为唯一(或PK).

• offer.id and sports.id are defined as primary key.
• sports.name is defined unique.
• (sport_id, offer_id) in offers_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:

• 如何过滤SQL导致具有多次通过关系

由@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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！