Rails ActiveRecord sort by count of join table associations

Question

I have a Resource model that can be voted on using the "Acts As Votable" gem (Github page). The voting system works perfectly but I am trying to display pages ordered by how many votes each Resource has.

Currently my controller pulls Resources based on tags and aren't ordered:

@resources = Resource.where(language_id: "ruby")

If I take an individual resource and call "@resource.votes.size" it will return how many votes it has. However, votes is another table so I think some sort of join needs to be done but I have not sure how to do it. What I need is a nice ordered ActiveRecord collection I can display like this?

Book name - 19 votes

Book name - 15 votes

Book name - 9 votes

Book name - 8 votes

Answer 1

Try the following:

@resources = Resouce.select("resources.*, COUNT(votes.id) vote_count")
                    .joins(:votes)
                    .where(language_id: "ruby")
                    .group("resources.id")
                    .order("vote_count DESC")

@resources.each { |r| puts "#{r.whatever}  #{r.vote_count}" }

To include resources with 0 votes, use an outer join. If the example below doesn't work as is you'll have to alter the joins statement to join across the correct relations.

@resources = Resource.select("resources.*, COUNT(votes.id) vote_count")
                     .joins("LEFT OUTER JOIN votes ON votes.votable_id = resources.id AND votes.votable_type = 'Resource'")
                     .where(language_id: "ruby")
                     .group("resources.id")
                     .order("vote_count DESC")

Answer 2

Rails 5+

Built-in support for left outer joins was introduced in Rails 5 so you can use that to do this. This way you'll still keep the records that have 0 relationships:

Resource
  .where(language_id: 'ruby')
  .left_joins(:votes)
  .group(:id)
  .select('resources.*', 'COUNT(votes.id) vote_count')
  .order('vote_count DESC')

Answer 3

You need to apply Group clause like this:

@resources = Resource.select('resources.*, count(votes.id) as votes_count').
  joins(:votes).group(:id).order('votes_count DESC')

Answer 4

It would return the resources that have the most votes

@resources.joins(:votes).group("resources.id").order("count(resources.id) DESC")