Ruby-on-rails Rails 按 has_many 关联的结果计数排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16996618/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails order by results count of has_many association
提问by randika
Is there anyway I can order the results (ASC/DESC) by number of items returned from the child model (Jobs)?
无论如何我可以按从子模型 ( )返回的项目数对结果 ( ASC/ DESC) 进行排序Jobs吗？
@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
例如：我需要在顶部打印职位最高的公司
采纳答案by Billy Chan
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
回答by Sheharyar
Rails 5+
导轨 5+
Support for left outer joins was introduced in Rails 5so you can use an outer join instead of using counter_cacheto 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_sqlon 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
回答by Billy Chan
Something like:
就像是：
Company.joins(:jobs).group("jobs.company_id").order("count(jobs.company_id) desc")
回答by Tan
@user24359 the correct one should be:
@user24359 正确的应该是：
Company.joins(:jobs).group("companies.id").order("count(companies.id) DESC")
回答by Christian Angel Galamay
Added to Tan's answer. To include 0 association
添加到 Tan 的回答中。包含 0 个关联
Company.joins("left join jobs on jobs.company_id = companies.id").group("companies.id").order("count(companies.id) DESC")
by default, joinsuses inner join. I tried to use left jointo include 0 association
默认情况下，joins使用内部联接。我尝试使用left join包含 0 个关联
回答by faisal bhatti
Company.where("condition here...")
.left_joins(:jobs)
.group(:id)
.order('COUNT(jobs.id) DESC')
.limit(10)
