rails ordering by has many relation

Question

I'm trying to order my concerts by the number of reviews each has. Concert has_many reviews. Tried doing this

<% @concerts = Concert.all %>
<% @concerts.order(reviews.size).each do |concert| %>
  -
  -
  -
<% end %>

but I get this error

undefined method `review' for ActiveRecord::Relation::ActiveRecord_Relation_Concert:0xb5540398>

Any suggestions how I would reference the number of reviews each has to order the concerts?

Untested, but this should work: Concert.joins(:reviews).group('reviews.concert_id').order('COUNT(reviews.id)') — rails4guides.com, Apr 13 '14 at 20:38
doesn't work: ERROR: column "concerts.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT "concerts".* FROM "concerts" INNER JOIN "reviews" ON ... — parameter, Apr 13 '14 at 20:41

score 3 · Accepted Answer · answered Apr 13 '14 at 20:44

3

Not the best, but the simplest solution is

@concerts = Concert.all.includes(:reviews).sort_by{|concert| concert.reviews.size}.reverse

answered Apr 13 '14 at 20:44

Mikhail Chuprynski

2,404
2
29
42

so this works for least to most reviews, how would it be for most to least? – parameter Apr 13 '14 at 20:47

Paul Richter · Answer 2 · 2014-04-14T00:55:13.250

An alternative to the other answer which will ultimately give you the same result set, but will have slightly different side effects:

Concert.select("concerts.*, count(reviews.id) review_count")
       .joins("LEFT JOIN reviews ON concerts.id = reviews.concert_id")
       .group("concerts.id")
       .order("review_count")

The main difference is that this query will not immediately execute until it is used; you'll receive an active record relation, just as you normally would when using any of the active record query methods, which means you can further refine or add to the query (hypothetically speaking).

Another difference is that this query does not require eager loading of reviews. Depending on your needs, if you don't require any information from the related reviews, this query will run considerably faster.

As far as timing / performance goes, using a database of 50 concerts and 43867 reviews (index exists on FK), both versions seem to execute in approximately the same time (with eager loading). Here's a table of my benchmarking results (all results are in seconds):

| # | Sory_by Query | Pure AR Query | No eager loading |
--------------------------------------------------------
| 1 | 2.899806      | 2.840702      | 0.02164          |
| 2 | 3.150556      | 2.818374      | 0.21612          |
| 3 | 2.812867      | 3.025921      | 0.016368         |
| 4 | 3.069562      | 3.055307      | 0.016884         |
| 5 | 2.862722      | 2.680357      | 0.021316         |
|---|---------------|---------------|------------------|
 AVG: 2.9591026     | 2.8841322     | 0.0584836        |

As you can see, there's no significant difference between the two queries using eager loading, and a major difference using my query above without eager loading. Obviously, the exact results will be different for you, but this should give you an idea of the relative differences.

Side note: From what you posted in the question, it seems you are/were wanting to write this query in the ERB view. I would strongly advice moving this query to a method in the Concert model, and creating an instance variable from the return of that method in the controller which the view can then use. That way you keep everything nice and separated.

Edit To illustrate my recommendation, I would place the query in a class method of the Concert model, like so:

def self.ordered_by_reviews
    return select("concerts.*, count(reviews.id) review_count")
          .joins("LEFT JOIN reviews ON concerts.id = reviews.concert_id")
          .group("concerts.id")
          .order("review_count")
end

Which you can call from your controller (doesn't matter which controller):

... other controller code:
@concerts = Concert.ordered_by_reviews
... and so on

You can then use @concerts as you need and can remove any stuff like @concerts = Concert.all and such.

Alternatively, you can also use scopes to do the same thing (which I believe would be considered more Rails-y anyways):

class Concert < ActiveRecord::Base
    scope :ordered_by_review, -> { select("concerts.*, count(reviews.id) review_count").joins("LEFT JOIN reviews ON concerts.id = reviews.concert_id").group("concerts.id").order("review_count") }
    ... rest of class

Thank you for this. I was actually using it within the ERB view for Reviews. I'll look into making it a method for Concert — parameter, Apr 13 '14 at 23:50
It would need to go into the Concert controller right? I should also note that I only want to do this for maybe 10 concerts, but that doesn't really matter because I have to organize all of them regardless before displaying the first 10 i suppose — parameter, Apr 13 '14 at 23:55
@user2739431 Ok well you shouldn't have any trouble appending something like `.limit(10)`, or some `.where` clause parameters, however you intend to limit them to ~10. What'll happen is the whole thing will be sorted, and then filtered by whatever `.where` or `limit` parameters you specify. As for your question *would need to go into the Concert controller*, not 100% sure what you mean, can you elaborate? I'll edit the question and add an example of where I meant the query should go, if that's what you mean. — Paul Richter, Apr 14 '14 at 00:47

rails ordering by has many relation

2 Answers2