Get all records where the number of associated records is smaller than a certain number

Question

I have a model Occurrence that has many Cleaners through the joint table Assignments. The Occurrence model has a field number_of_cleaners.

How can I find all Occurrences using Active Record (or SQL, Postgres) where the number of assigned Cleaners is smaller than the number specified in occurrences.number_of_cleaners?

This query is to identify the Occurrences where we need to find more Cleaners to assign to the Occurrence).

class Occurrence < ActiveRecord::Base
  belongs_to :job
  has_many :assignments
  has_many :cleaners, through: :assignments
end

class Assignment < ActiveRecord::Base
  belongs_to :cleaner
  belongs_to :occurrence
end

Just as a side note, previously we just queried for each Occurrence that had no Assignment regardless of occurrences.number_of_cleaners. The query looked like this:

# Select future occurrences which do not have an assignment (and therefore no cleaners) and select one per job ordering by booking time
# The subquery fetches the IDs of all these occurrences
# Then, it runs another query where it gets all the IDs from the subquery and orders the occurrences by booking time
# See http://stackoverflow.com/a/8708460/1076279 for more information on how to perform subqueryes
subquery = Occurrence.future.where.not(id: Assignment.select(:occurrence_id).uniq).select('distinct on (job_id) id').order('occurrences.job_id', 'booking_time')
@occurrences = Occurrence.includes(job: :customer).where("occurrences.id IN (#{subquery.to_sql})").where.not(bundle_first_id: nil).select{ |occurrence| @current_cleaner.unassigned_during?(occurrence.booking_time, occurrence.end_time) }

Do you need all the cleaners of that occurrence based on that number, or you need all the occurrences? — Sravan, Feb 12 '16 at 06:54

score 1 · Accepted Answer · answered Feb 12 '16 at 07:37

Instead of joining the tables and doing query, You should implement counter_cache on your models as its more efficient and meant exactly for the purpose.

For more details, check out these links:

Counter Cache in Rails

Three Easy Steps to Using Counter Caches in Rails

Get all records where the number of associated records is smaller than a certain number

1 Answers1