CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-3activerecord
randika
randika

Reputation: 1539

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

Upvotes: 68

Views: 56027

Answers (7)

r4cc00n
r4cc00n

Reputation: 2137

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

Upvotes: 0

Christian Angel Galamay
Christian Angel Galamay

Reputation: 129

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

Upvotes: 6

faisal bhatti
faisal bhatti

Reputation: 325

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

Upvotes: -5

Sheharyar
Sheharyar

Reputation: 75840

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

Upvotes: 132

Tan Nguyen
Tan Nguyen

Reputation: 3376

@user24359 the correct one should be:

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

Upvotes: 29

Billy Chan
Billy Chan

Reputation: 24815

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

Upvotes: 53

user24359
user24359

Reputation:

Something like:

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

Upvotes: 28

Related Questions