Question 362444 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – ActiveRecord query for count of association using a join table

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.

Tags:

2

Answers


  1. 0

    I think you don’t need any complex joins you can do it like this.

    Since relation is as follows

    1. Event has_one Party
    2. Party has_many UserParty

    You can do it like this.

    # event.rb
scope :accepting_members, -> { party.user_parties.count < party.member_limit }

    This will return boolean. if member limit is not reached it will return true otherwise false.

    Login or Signup to reply.
  2. 0

    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
  LEFT JOINS user_parties ON parties.id = user_parties.party_id
  GROUP parties.event_id
  HAVING(
    COUNT(user_parties.user_id) < parties.member_limit
  )
)

    Writing this in ActiveRecord/Arel gets a bit hairy but bear with me:

    subquery = Party.select(:events_id)
                .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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search