SQL中的
我创建了一个候选模型，该模型与响应模型具有has_many关联
在响应模型中，我们还将状态存储为挂起和已完成
我想添加根据挂起状态的号码订购候选人的范围
E.g
Candidates1 -> repsones1 --> {status: "pending"}
-> repsones2 --> {status: "completed"}
*******************************************************
Candidates2 -> repsones1 --> {status: "pending"}
-> repsones2 --> {status: "pending"}
*******************************************************
Candidates3 -> repsones1 --> {status: "completed"}
-> repsones2 --> {status: "completed"}
*******************************************************
Candidates4 -> repsones1 --> {status: "pending"}
-> repsones2 --> {status: "pending"}
所以从上面的例子来看，我想像这个一样订购
Candidates2 #pending status count is 2
Candidates4 #pending status count is 2
Candidates1 #pending status count is 1
Candidates3 #pending status count is 0
我正在使用Postgres作为数据库
我不想在候选型号上添加任何计数属性
我尝试了一些范围
class Candidate < ActiveRecord::Base
has_many :responses
scope :order_by_pending_responses, -> {
joins(:responses)
.order("responses.status='pending' DESC")
}
end
但是得到重复的候选者如果我在scope上添加了uniq关键字，那么在分页上会出现错误
ORDER BY子句可以包括聚合和条件：
scope :order_by_pending_responses, -> {
left_joins(:responses)
.group("candidates.id")
.order("SUM(CASE WHEN responses.status = 'pending' THEN 1 ELSE 0 END) DESC")
}
试试这个：
scope :order_by_pending_responses, -> {
left_joins(:responses)
.where(responses: { status: :pending })
.group("candidates.id")
.order("count(responses.id) DESC")
}
然而，如果您要经常调用此范围，我建议您使用内置的counter_cache：
class Response < ActiveRecord::Base
belongs_to :candidate, counter_cache: true
# ...
end
# add a migration
add_column :candidates, :responses_count, :integer, default: 0
# Candidate model
class Candidate < ActiveRecord::Base
scope :order_by_pending_responses, order('responses_count DESC')
# ...
end