Multiple joins with count and having with ActiveRecord

Question

My application is about Profiles that have many Wishes, that are related to Movies:

class Profile < ApplicationRecord
  has_many :wishes, dependent: :destroy
  has_many :movies, through: :wishes
end

class Wish < ApplicationRecord
  belongs_to :profile
  belongs_to :movie
end

class Movie < ApplicationRecord
  has_many :wishes, dependent: :destroy
  has_many :profiles, through: :wishes
end

I would like to return all the Movies that are all "wished" by profiles with id 1,2, and 3.

I managed to get this query using raw SQL (postgres), but I wanted to learn how to do it with ActiveRecord.

select movies.id
    from movies
    join wishes on wishes.movie_id = movies.id
    join profiles on wishes.profile_id = profiles.id and profiles.id in (1,2,3)
    group by movies.id
    having count(*) = 3;

(I'm relying on count(*) = 3 because I have an unique index that prevents creation of Wishes with duplicated profile_id-movie_id pairs, but I'm open to better solutions)

At the moment the best approach I've found is this one:

profiles = Profile.find([1,2,3])
Wish.joins(:profile, :movie).where(profile: profiles).group(:movie_id).count.select { |_,v| v == 3 }

(Also I would begin the AR query with Movie.joins, but I didn't manage to find a way :-)

Answer 1

Since what we want is a collection of Movies, the ActiveRecord query needs to start from Movie. What I was missing is that we can specify the table in the query, like where(profiles: {id: profiles_ids}).

Here is the query I was looking for: (yes, using count might sound a bit brittle, but the alternative was an expensive SQL subquery. Also, I think it's safe if you're using a multiple-column unique index.)

profiles_ids = [1,2,3]
Movie.joins(:profiles).where(profiles: {id: profiles_ids}).group(:id).having("COUNT(*) = ?", profiles_ids.size)