How to Query for Offers Containing a Specific Set of Sports in a Many-to-Many Relationship?

SQL Error: "where joined set must contain all values but may contain more" for Offers and Sport Filters

In our database, we have three tables: offers, sports, and the join table offers_sports. We want to retrieve offers that include a given set of sports, with the requirement that all specified sports must be present, but additional sports may also be included.

For instance, considering the following offers:

• Light: Yoga, Bodyboarding
• Medium: Yoga, Bodyboarding, Surfing
• All: Yoga, Bodyboarding, Surfing, Parasailing, Skydiving

If we query for offers containing "Bodyboarding" and "Surfing," we expect to receive both "Medium" and "All" but not "Light." However, our current query:

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

And the SQL equivalent:

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;

Return no results.

To rectify this, we modify our query to group by the offer ID instead of the sport name:

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;

or in 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

This modification ensures that we appropriately group the results and apply the necessary filtering criteria to retrieve the desired offers that include the specified sports.

The above is the detailed content of How to Query for Offers Containing a Specific Set of Sports in a Many-to-Many Relationship?. For more information, please follow other related articles on the PHP Chinese website!