作用域按计数排序 [英] Scope Order by Count with Conditions Rails

问题描述

我有一个模型 Category ，该模型 has_many Pendencies .我想创建一个范围，该范围按具有 active = true 的倾向量对类别进行排序，而不排除 active = false .

I have a model Category that has_many Pendencies. I would like to create a scope that order the categories by the amount of Pendencies that has active = true without excluding active = false.

到目前为止，我有:

scope :order_by_pendencies, -> { left_joins(:pendencies).group(:id).order('COUNT(pendencies.id) DESC')}

这将根据未决顺序进行排序，但是我想按具有 active = true 的未决顺序进行排序.

This will order it by number of pendencies, but I want to order by pendencies that has 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 的未决数目.

This will order by number of pendencies that has pendencies.active = true, but will exclude the pendencies.active = false.

谢谢您的帮助.

推荐答案

我想您想按活动的活动量进行排序，而不忽略没有活动的活动类别.

I guess you want to sort by the amount of active pendencies without ignoring categories that have no active pendencies.

那将是这样的:

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，并将被添加到列表的末尾.可以添加一个类似的子查询，以根据未完成的总金额进行排序...

Note that if there are no active pendencies for a category, the count will be null and will be added to the end of the list. A similar subquery could be added to sort additionally by the total amount of pendencies...

这篇关于作用域按计数排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！