当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - Rails 4 ActiveRecord order by count of association

问题描述:

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.

网友答案:

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
分享给朋友:
您可能感兴趣的文章:
随机阅读: