CODEWITHSUNDEEP
ruby-on-rails-4has-manydatabase-relations
parameter
parameter

Reputation: 904

rails ordering by has many relation

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?

Upvotes: 3

Views: 782

Answers (2)

Paul Richter
Paul Richter

Reputation: 11072

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

Upvotes: 2

Mikhail Chuprynski
Mikhail Chuprynski

Reputation: 2493

Not the best, but the simplest solution is

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

Upvotes: 3

Related Questions