Rails order by results count of has_many association

Question

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

Answer 1

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 

Answer 2

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