Rails 4 ActiveRecord order by count of association

Question

There's a view where I want to display the contents of

for example:

recipe.user.name 
recipe.name
recipe.picture
recipe.created_at

sorted by

recipe.likes.count

Controller:

@recipes_by_likes = Recipe.likes_count.paginate(page: params[:page])

Model:

Recipe has_many :likes

and

Like belongs_to :recipe

Scope:

scope :likes_count, -> { 
  select('recipes.*, COUNT(likes.id) AS likes_count').
  joins(:likes).
  order('likes_count DESC')
}

but that gives me

SELECT  recipes.*, COUNT(likes.id) AS likes_count 
FROM "recipes" 
INNER JOIN "likes" ON "likes"."recipe_id" = "recipes"."id"  
ORDER BY "recipes"."created_at" DESC, likes_count DESC 
LIMIT 30 OFFSET 0

which returns only 1 (wrong) result.

Answer 1

1. Query

You should not count likes by their id since it is always unique and will always return 1, instead by the recipe_id which is the same in the likes associated with a certain recipe.

scope :likes_count, -> { 
  select('recipes.*, COUNT(likes.recipe_id) AS likes_count')
  .joins(:likes)
  .group('recipes.id')
  .order('likes_count DESC')
}

2. Counter Cache Method

Also you can use a counter_cache

class Like < ActiveRecord::Base
  belongs_to :recipe, counter_cache: true
end

And add a column to Recipe model

add_column :recipes, :likes_count, :integer, default: 0