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

Sheharyar · Answer 1 · 2017-07-19T16:04:44.373

129

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

edited Jul 19 '17 at 16:04

answered Mar 17 '17 at 01:07

Sheharyar

73,588
21
168
215

2

this should be the really works answer, if you remove `.limit(10)`, you can get the companies have 0 jobs while other answers you will miss the 0 jobs companies. – Spark.Bao May 02 '17 at 14:38
1

I'm still using Rails 4. Can you show me the raw SQL for this? – ironsand Jul 18 '17 at 21:44
1

This does not seem to work for associations. For example if a `SalesPerson` has many `Companies` as customers through deals. Then `sales_person.companies.left_joins(:jobs)...` would result in the database error "SELECT DISTINCT, ORDER BY expressions must appear in select list". – Christoffer Reijer Sep 20 '21 at 21:20

score 52 · Accepted Answer · edited Aug 20 '15 at 14:08

52

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

edited Aug 20 '15 at 14:08

Roope Hakulinen

7,326
4
43
66

answered Jun 08 '13 at 06:58

Billy Chan

24,625
4
52
68

6

Well I really don't want to add extra column for this. – randika Jun 08 '13 at 09:38
2

It depends. There is no perfect solution. If performance and clean code have priority, adding a column doesn't hurt at. – Billy Chan Jun 08 '13 at 10:08
5

But then you have to update `companies.jobs_count` table for each insertion on the `jobs` table. That's 2 writes on 2 different tables. Where's the gain? – Yaw Boakye Jul 20 '13 at 19:44
4

Very helpful, +1. AFAICS, there is only thing missing: if the DB has data, you'll start with a count=0 unless you do this: `Company.find_each { |company| Company.reset_counters(company.id, :jobs) }`. – tokland Nov 20 '14 at 21:30
1

This is by far the best answer, also see http://ryan.mcgeary.org/2016/02/05/proper-counter-cache-migrations-in-rails/ for information on writing a migration that allow for extremely quick updating of the counter_cache via SQL. – Kyle Ratliff May 05 '20 at 22:45
Thanks man, this answer helped me a lot!!! – Jigar Bhatt Aug 09 '23 at 16:23

score 28 · Answer 3 · answered Jun 08 '13 at 06:17

28

Something like:

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

answered Jun 08 '13 at 06:17

21

With this, if the object has 0 relationships it does disappear. – kinunt Sep 26 '14 at 17:57
there's a gem left_join that can be used. But beware, nulls will appear first or last depending on the order direction. You can put 'em alwas last looking at this http://stackoverflow.com/questions/5826210/rails-order-with-nulls-last/7055259#7055259 – juliangonzalez Jan 14 '16 at 21:19

Tan Nguyen · Answer 4 · 2017-05-13T04:17:58.080

28

@user24359 the correct one should be:

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

edited May 13 '17 at 04:17

answered Nov 10 '15 at 04:33

Tan Nguyen

3,281
1
18
18

score 6 · Answer 5 · answered May 21 '19 at 03:56

6

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

answered May 21 '19 at 03:56

Christian Angel Galamay

109
1
6

score 0 · Answer 6 · edited Dec 30 '20 at 00:48

0

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

edited Dec 30 '20 at 00:48

Jannik Schmidtke

1,257
2
5
15

answered Dec 30 '20 at 00:41

r4cc00n

1,927
1
8
24

score -5 · Answer 7 · edited Nov 29 '17 at 14:47

-5

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

edited Nov 29 '17 at 14:47

Sebastián Palma

32,692
6
40
59

answered Jul 25 '17 at 10:31

faisal bhatti

325
2
12

7

This is a clear duplicate of my answer, you just added an extra `where` statement. – Sheharyar Dec 13 '18 at 23:16

Rails order by results count of has_many association

7 Answers7

Rails 5+

Linked

Related