0

I have a rails app with "recipes" model. Recipe can be liked ("like" model) by a user. What I'd like to achieve (eventually) is to sort the list of the recipes on the recipes-index page by the number of likes.

I've seen numerous threads but the two that guided me the most were:

Rails 3 ActiveRecord: order and group by association count

Rails order by results count of has_many association

I've got so far that I have an SQL-query that produces list of the recipes with the count of their likes:

SELECT recipes.name, COUNT(likes.id)
FROM recipes
LEFT JOIN likes on recipes.id = likes.recipe_id
GROUP BY recipes.name
ORDER BY COUNT(likes.id) DESC;

However, when "translating" it to something ActiveRecord can understand, I'm running into troubles.

I've concluded that using Recipe.joins(:likes) or Recipe.left_joins(:likes) passes all columns specified in "recipes", which leads to the following error:

"Column "recipes.id" must appear in the GROUP BY clause or be used in an aggregate function...".

Now, as I need to pass recipes.name and COUNT(likes.id) only, I used select:

Recipe.select("recipes.name, COUNT(likes.id)").joins(:likes).group("recipes.name").order("COUNT(likes.id) DESC")

And that's where I'm stranded as it produces this:

ActiveRecord::Relation [Recipe id: nil, name: "Scrambled eggz", Recipe id: nil, name: "Soup", Recipe id: nil, name: "Nutella pancakes"]

tomdonarski
  • 49
  • 2
  • 8

3 Answers3

1
Recipe.joins("LEFT JOIN likes ON recipes.id = likes. recipe_id").group("recipes.id").order("COUNT(likes.id) ASC")

ActiveRecord parsing expects the returned resultset to have the columns that the original model has(Or a subset of columns).

When selecting a subset of the original columns, ActiveRecord nullifies all the other columns while parsing it in an ActiveRecord object/relation

Zaid Annas
  • 87
  • 3
  • please elaborate a bit. What does all these functions do and why does this work. Just dropping a line of code is not an awesome answer. Please educate future visitors. – rene Jun 11 '19 at 07:00
1

This may help you if you want to it in active record query:

Recipe.left_joins(:likes).group(:id).order("COUNT(likes.id) DESC")

Warning for the JOIN:

Recipe.joins(:likes).group(:id).order("COUNT(likes.id) DESC")

This query will only return the recipes which have likes. If there are any recipes that don't have any likes, doing JOINS won't get you those.

Tamal Chakroborty
  • 71
  • 3
0

Your query is correct. Please note that it use Recipe model class, which does not contains your custom field in its Class. If your query is 

Recipe.select("COUNT(likes.id)").joins(:likes).group("recipes.name").order("COUNT(likes.id) DESC")

It will show you 

ActiveRecord::Relation [Recipe id: nil, Recipe id: nil, Recipe id: nil]

However, You still can access your custom fields, the returned data hold it for you to use. You just need to give it a name to call it. Also, You can use that custom field to order, please don't direct order COUNT

recipes = Recipe.select("COUNT(likes.id) as total_likes").joins(:likes).group("recipes.name").order("total_likes DESC")
recipe.first.total_likes # TOTAL LIKE of first recipe
quyetdc
  • 1,485
  • 1
  • 14
  • 24