How to Select Offers Containing All of a Specified Set of Sports in SQL?

"SQL where joined set must contain all values but may contain more"

Understanding the Requirement

Consider three related tables: offers, sports, and offers_sports, joined through has_and_belongs_to_many relationships. The goal is to select offers that include a specified array of sports, where the offers must contain all the specified sports but may have additional sports.

Potential Solution

One approach is to use a combination of JOIN, WHERE, GROUP BY, and HAVING clauses to filter the results based on the required conditions. Specifically:

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;

This query first joins the three tables to retrieve offers and their associated sports. The WHERE clause limits the search to sports with specified names. The GROUP BY clause groups the results by offer ID, and the HAVING clause ensures that only offers containing all specified sports are selected.

Improved ActiveRecord Implementation

The provided ActiveRecord solution can be further refined to enhance efficiency:

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

This method uses chaining to perform the necessary operations, including JOIN, WHERE, GROUP BY, and HAVING, in a single query. It also simplifies the HAVING clause by using a question mark (?) placeholder to dynamically pass the expected count value.

The above is the detailed content of How to Select Offers Containing All of a Specified Set of Sports in SQL?. For more information, please follow other related articles on the PHP Chinese website!