如何基于Follows表数据对Ruby ActiveRecord集合排序？

问题背景 #

现有筛选后的Company的ActiveRecord::Relation集合：

#<ActiveRecord::Relation [#<Company id: 483, name: "Apple", description: "Apple Inc. is an American multinational technology...", social_website: "https://www.apple.com/">, #<Company id: 482, name: "Ceres Technology", description: "", social_website: "cerestech.co">]>

另有follows表记录公司粉丝数据，原代码通过循环统计一周内各公司粉丝数：

query_chain.each do |company|
  total_followers_period = Follow.where('followable_type = ? AND followable_id = ? AND created_at > ?', "Company", company.id,1.week.ago).count
  total_followers_companies[company.id] =  total_followers_period
end

需要按一周内粉丝数对Company集合排序，且必须返回ActiveRecord::Relation而非数组。

解决方案 #

可以通过关联查询+分组统计直接生成带排序的ActiveRecord::Relation，避免循环查询的N+1问题，同时满足返回关系对象的要求：

方法1：LEFT JOIN 原生查询 #

Company.joins("LEFT JOIN follows ON follows.followable_type = 'Company' AND follows.followable_id = companies.id AND follows.created_at > '#{1.week.ago.to_s(:db)}'")
       .select("companies.*, COUNT(follows.id) AS weekly_followers_count")
       .group("companies.id")
       .order("weekly_followers_count DESC")

方法2：基于ActiveRecord关联（推荐） #

先在Company模型中定义多态关联：

class Company < ApplicationRecord
  has_many :follows, as: :followable
end

之后用更简洁的链式查询：

# 直接查询所有公司并排序
Company.left_joins(:follows)
       .where(follows: { created_at: 1.week.ago..Time.current })
       .select("companies.*, COUNT(follows.id) AS weekly_followers_count")
       .group("companies.id")
       .order("weekly_followers_count DESC")

# 基于原筛选后的query_chain排序
query_chain.left_joins(:follows)
           .where(follows: { created_at: 1.week.ago..Time.current })
           .select("companies.*, COUNT(follows.id) AS weekly_followers_count")
           .group("companies.id")
           .order("weekly_followers_count DESC")

关键说明 #

• 两种方法都在数据库层面完成统计和排序，性能远优于循环查询
• 返回结果仍为ActiveRecord::Relation，支持后续继续链式调用其他查询方法
• 统计出的粉丝数会以weekly_followers_count属性的形式附加到每个Company对象上，可直接访问

内容的提问来源于stack exchange，提问作者Veka0881