CODEWITHSUNDEEP
ruby-on-railsactiverecord
user3574603
user3574603

Reputation: 3628

Rails/ActiveRecord: How do I order records by the most associations with a related model?

I have two models.

class Tourist < ApplicationRecord
  has_many :visits
  has_many :countries, through: :visits
end

class Country < ApplicationRecord
  has_many :visits
  has_many :tourists, through: :visits
end

class Visit < ApplicationRecord
  belongs_to :country
  belongs_to :tourist
end

I am trying to find a way to sort the countries by the number of tourist associations.

I'm led to believe I should join the tourist's table with countries, then group by tourist before ordering to place the group with the largest count at the top.

Country.joins(:tourist).group(:tourist_id).order('COUNT(*)')

However, this doesn't seem to be quite right. Firstly, I get a depreciation warning.

DEPRECATION WARNING: Dangerous query method (method whose arguments are used as raw SQL) called with a non-attribute argument(s): "COUNT(*)". Non-attribute arguments will be disallowed in Rails 6.0. This method should not be called with user-provided values, such as request parameters or model attributes. Known-safe values can be passed by wrapping them in Arel.sql(). (called from __pry__ at (pry):61)

Also, I don't think I'm getting the correct values because everything after first is nil.

What is the correct way to get the records with the most associations?

Upvotes: 1

Views: 1109

Answers (2)

ray
ray

Reputation: 5552

I did not test but following must work,

Country.joins(:tourists).group('tourists.id').order('COUNT(tourists.id)')

It will sort countries collection by number of associated tourists count.

To get largest count at top, change order as below,

Country.joins(:tourists).group('tourists.id').order('COUNT(tourists.id) DESC')

Upvotes: 3

Rohit Lingayat
Rohit Lingayat

Reputation: 716

You can use left_joins for this query

Country.left_joins(:tourists).group('tourists.id').order('COUNT(tourists.id)')

Upvotes: 0

Related Questions