ActiveRecord query for count of association using a join table

Question

I'm working an events feature, and I'm running into a rather involved query.

Here's the models for reference:

class Event < ApplicationRecord
  has_one :party

  belongs_to :host, foreign_key: :host_id, class_name: 'User'
  belongs_to :convention, optional: true
  has_many :join_requests
  
  ...
end

I'm looking to check which Events are accepting members, by checking the member_limit of the associated Party.

class Party < ApplicationRecord
  belongs_to :event
  belongs_to :host, foreign_key: :host_id, class_name: 'User'

  has_many :user_parties
  has_many :members, through: :user_parties, source: 'user'

  ...
end

Party has an integer column: member_limit -- which only allows a set amount of party members.

So far I have a scope on Event that looks like this:

scope :accepting_members, -> () do
    joins(party: :user_parties).group('parties.member_limit').select('count(user_parties.user_id) < parties.member_limit')
  end

Which doesn't work too well.

Am I on the right track?

I tried using the COUNT() aggregate, but it only returns a hash of integers.

Answer 1

This is going to be a bit more complicated then you initially thought if you want usable output and there are quite a few ways to do this.

One way is to create a subquery for the event ids:

SELECT events.* 
FROM events
WHERE events.id IN (
  SELECT parties.events_id
  FROM parties
  OUTER LEFT JOIN user_parties ON parties.id = user_parties.party_id
  GROUP BY parties.event_id
  HAVING(
    COUNT(user_parties.user_id) < parties.member_limit
  )
)

Writing this in ActiveRecord/Arel gets a bit hairy:

subquery = Party.select(:events_id)
                .left_joins(:user_parties)
                .group(:event_id)
                .having(
                  # COUNT(user_parties.user_id) < parties.member_limit
                  UserParty.arel_table[:user_id].count.lt(
                    Party.arel_table[:member_limit]
                  )
                )
                .where(
                  # events.id = parties.event_id
                  Event.arel_table[:id].eq(Party.arel_table[:id])
                )
 
Event.where(
  id: subquery
)

Other ways to do this would be to use EXIST or a lateral join.