Rails order by results count of has_many association

Is there anyway I can order the results (ASC/DESC) by number of items returned from the child model (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

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:

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):

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

If you expect to use this query frequently, I suggest you to use built-in 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

Something like:

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

@user24359 the correct one should be:

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

Added to Tan's answer. To include 0 association

Company.joins("left join jobs on jobs.company_id = companies.id").group("companies.id").order("count(companies.id) DESC")

by default, joins uses inner join. I tried to use left join to include 0 association

Adding to the answers, the direct raw SQL was removed from rails 6, so you need to wrap up the SQL inside Arel (if the raw SQL is secure meaning by secure avoiding the use of user entry and in this way avoid the SQL injection).

Arel.sql("count(companies.id) DESC")

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