Rails按结果计数has_many关联

Is there anyway I can order the results (ASC/DESC) by number of items returned from the child model (Jobs)?

无论如何，我可以按子模型（工作）返回的项目数量来订购结果（ASC / DESC）吗？

@featured_companies = Company.joins(:jobs).group(Job.arel_table[:company_id]).order(Job.arel_table[:company_id].count).limit(10)

For example: I need to print the Companies with highest jobs on top

例如：我需要打印最高职位的公司

5 个解决方案

#1

If you expect to use this query frequently, I suggest you to use built-in counter_cache

如果您希望经常使用此查询，我建议您使用内置的counter_cache

# Job Model
class Job < ActiveRecord::Base
  belongs_to :company, counter_cache: true
  # ...
end

# add a migration
add_column :company, :jobs_count, :integer, default: 0

# Company model
class Company < ActiveRecord::Base
  scope :featured, order('jobs_count DESC')
  # ...
end

and then use it like

然后像使用它一样

@featured_company = Company.featured

#2

Rails 5+

Support for left outer joins was introduced in Rails 5 so you can use an outer join instead of using counter_cache to do this. This way you'll still keep the records that have 0 relationships:

在Rails 5中引入了对左外连接的支持，因此您可以使用外连接而不是使用counter_cache来执行此操作。这样您仍然可以保留具有0个关系的记录：

Company
  .left_joins(:jobs)
  .group(:id)
  .order('COUNT(jobs.id) DESC')
  .limit(10)

---

The SQL equivalent of the query is this (got by calling .to_sql on it):

查询的SQL等价物是这个（通过调用.to_sql获得）：

SELECT "companies".* FROM "companies" LEFT OUTER JOIN "jobs" ON "jobs"."company_id" = "companies"."id" GROUP BY "company"."id" ORDER BY COUNT(jobs.id) DESC

#3

Something like:

就像是：

Company.joins(:jobs).group("jobs.company_id").order("count(jobs.company_id) desc")

#4

@user24359 the correct one should be:

@ user24359正确的应该是：

Company.joins(:jobs).group("companies.id").order("count(companies.id) DESC")

#5

Company.where("condition here...")
       .left_joins(:jobs)
       .group(:id)
       .order('COUNT(jobs.id) DESC')
       .limit(10)