范围Order by Count with Conditions Rails

Question

我有一个has_many Pendencies的模型Category。我想创建一个范围，在不排除active = false的情况下，根据具有active = true的挂起数量对类别进行排序。

到目前为止，我所拥有的是：

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。

谢谢你的帮助。

Answer 1

我猜你想要按活动挂起的数量排序，而不是忽略没有活动挂起的类别。

这大概是这样的：

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

请注意，如果某个类别没有活动挂起，则计数将为空，并将添加到列表的末尾。可以添加一个类似的子查询，以根据挂起的总数进行额外的排序...

Answer 2

请求的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。

Answer 3

就目前而言，我开发了以下内容(它是有效的，但我认为这不是最好的方法)：

  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}