我有一个模型Category
has_many
Pendencies
.我想创建一个范围,按active = true
的Pendencions数量对类别进行排序,而不排除active = false
。
到目前为止,我所拥有的是:
scope :order_by_pendencies, -> { left_joins(:pendencies).group(:id).order('COUNT(pendencies.id) DESC')}
这将按悬垂数量排序,但我想按active = true
的悬垂排序.
另一个尝试是:
scope :order_by_pendencies, -> { left_joins(:pendencies).group(:id).where('pendencies.active = ?', true).order('COUNT(pendencies.id) DESC')}
这将按pendencies.active = true
的悬停数排序,但将排除pendencies.active = false
。
谢谢你的帮助。
我想您想按活动悬停的数量进行排序,而不会忽略没有活动悬停的类别。
那将是这样的:
scope :order_by_pendencies, -> {
active_count_q = Pendency.
group(:category_id).
where(active: true).
select(:category_id, "COUNT(*) AS count")
joins("LEFT JOIN (#{active_count_q.to_sql}) AS ac ON ac.category_id = id").
order("ac.count DESC")
}
等效的 SQL 查询:
SELECT *, ac.count
FROM categories
LEFT JOIN (
SELECT category_id, COUNT(*) AS count
FROM pendencies
GROUP BY category_id
WHERE active = true
) AS ac ON ac.category_id = id
ORDER BY ac.count DESC
请注意,如果某个类别没有活动悬停,则计数将为 null,并将添加到列表的末尾。可以添加一个类似的子查询,以按悬念总数进行额外排序......
C# 根据要求回答:
method() {
....OrderBy((category) => category.Count(pendencies.Where((pendency) => pendency.Active))
}
或者在直接 SQL 中:
SELECT category.id, ..., ActivePendnecies
FROM (SELECT category.id, ..., count(pendency) ActivePendnecies
FROM category
LEFT JOIN pendency ON category.id = pendency.id AND pendnecy.Active = 1
GROUP BY category.id, ...) P
ORDER BY ActivePendnecies;
即使代码会将其丢弃,我们也必须在SQL中输出ActivePendnecies
,否则优化器有权丢弃ORDER BY
。
现在我开发了以下内容(它正在工作,但我相信这不是最好的方法(:
scope :order_by_pendencies, -> { scoped = Category.left_joins(:pendencies)
.group(:id)
.order('COUNT(pendencies.id) DESC')
.where('pendencies.active = ?', true)
all = Category.all
(scoped + all).uniq}