获取关联记录数量小于特定数量的所有记录

问题描述：

我有一个模型Occurrence，它有很多Cleaners通过联合表Assignments。 Occurrence型号的字段为number_of_cleaners。获取关联记录数量小于特定数量的所有记录

如何使用Active Record（或SQL，Postgres）找到所有Occurrences，其中分配的Cleaners的数量小于occurrences.number_of_cleaners中指定的数量？

该查询是为了识别Occurrences，我们需要找到更多的Cleaners来指定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

正如一个侧面说明，以前我们只是查询每个Occurrence是没有Assignment不管occurrences.number_of_cleaners。查询是这样的：

# 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://*.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) }

你需要根据这个数字的是发生的所有清洁剂，或者你需要所有出现？ – Sravan

我需要所有的事件 – migu

答

而是连接表和做查询的，应实现您的模型counter_cache为更高效，更准确的意思为宗旨。

有关详细信息，请查看以下链接：

Counter Cache in Rails

Three Easy Steps to Using Counter Caches in Rails

获取关联记录数量小于特定数量的所有记录

相关推荐