Ruby on Rails: Issue sorting index based on average review rating

Question

So I am trying to order my Provider's (similar to a User) index by average rating, with the highest at the top. It works, but adds an extra row for each Review that a Provider has. E.g. If I have one Provider with 3 Reviews, the Provider will show up 3 times in the Providers index. Rating is a column on Reviews and is an integer between 1-5.

provider.rb

class Provider < ActiveRecord::Base
  belongs_to :user, foreign_key: 'user_id', autosave: true
  has_many :reviews, dependent: :destroy
end

review.rb

class Review < ActiveRecord::Base
  belongs_to :user, foreign_key: 'user_id'
  belongs_to :provider, foreign_key: 'provider_id'
end

providers_controller.rb

class ProvidersController < ApplicationController
  before_action :set_provider, only: [:show, :edit, :update, :destroy]

 def index
   @providers = Provider.all
                 .joins(:reviews)
                 .order("reviews.rating DESC")
   @reviews = Review.find_by(params[:provider_id])
 end

index.html.erb

 <% @providers.each do |provider| %>
  <tr>
    <td><%= link_to provider.name, provider_path(provider.id) %></td>
    <td><%= provider.industry %></td>
    <td><%= provider.experience %></td>
    <td><%= provider.description %></td>
    <td><%= provider.reviews.average(:rating) %></td>
  </tr>
<% end %>

Its probably something really silly as I am quite new to Ruby + Rails, but I cant seem to figure it out/find an relevant info. I've tried adding "AVG(reviews.rating) DESC" to the order method but that gives me an SQLite exception error.

Any help would be muchly appreciated!!! Happy to provide any more info if needed.

possible duplicate of [How to average product review scores using activerecord and POSTGRES](http://stackoverflow.com/questions/9753909/how-to-average-product-review-scores-using-activerecord-and-postgres) — Marcus Ilgner, Apr 27 '15 at 19:39

ilan berci · Answer 1 · 2015-04-28T17:44:38.840

2

It gave you a sql error because your error has nothing to do with rails.

In SQL, if you are using an aggregate function such as avg(), then that column must appear in your select clause.

Provider.select("avg(reviews.rating) as avg_rating").joins(:reviews).order("avg_rating desc")

Edited due to comment below

edited Apr 28 '15 at 17:44

answered Apr 27 '15 at 19:38

ilan berci

3,883
1
16
21

Thanks for the help!! When I try this I get "SQLite3::SQLException: near "select": syntax error: SELECT select avg(reviews.rating) as avg_rating FROM "providers" INNER JOIN "users" ON "users"."id" = "providers"."user_id" INNER JOIN "reviews" ON "reviews"."user_id" = "users"."id" ORDER BY avg_rating desc". I'm not sure where the user_id would be coming from? If I try removing select I get a missing attribute error for provider's attributes. Any ideas? – Chris Brown Apr 28 '15 at 17:22
Do the same thing but pull the select out of the string rovider.select("avg(reviews.rating) as avg_rating").joins(:reviews).order("avg_rating desc") – ilan berci Apr 28 '15 at 17:44
Tried that but I get a missing attribute error when trying to access provider.name, provider.industry, etc. in my view. Do I need to change the way I access attributes in my view? – Chris Brown Apr 28 '15 at 17:54

score 1 · Accepted Answer · answered Apr 27 '15 at 20:35

I assume you are asking multiple results of the same provider, so I'm going to try and address that. When you join the provider and reviews table, you will get as many rows returned for each provider as that provider has reviews. This is because if we have a provider: {:id => 1, :reviews => [...]} and join that with these reviews: [{:id => 1, :provider_id => 1, :content => "blah"}, {:id => 2, :provider_id => 1, :content => "bar"}, {:id => 3, :provider_id => 1, :content => "foo"}]

SQL will return a table that looks something like:

provider_id, review_id, content
1             1           blah
1             2           bar
1             3           foo

In your case I think an appropriate solution would just be to eliminate the joins clause completely. Doing so should eliminate the duplicates, and you can still access the reviews through the providers has_many relation.

This makes sense and seems to be what is happening. However when I remove the joins clause I get no such column: reviews.rating. Because I am trying to order result by the average of a providers reviews.rating, I would need to use the average function within the clause, no? Thanks for the thoughts. — Chris Brown, Apr 28 '15 at 17:28

Ruby on Rails: Issue sorting index based on average review rating

2 Answers2