How can I sort my records by average rating?

Question

I have a table of venues which I'm displaying on the venues index page as partials. I also have a table of reviews where one venue can have many reviews and each review has a rating 1-5.

I'm trying to get the venues to display on the index page with the ones with the highest average rating at the top and descending.

The controller code looks like this:

Venues controller

def index
    if
      @venues = Venue.with_type(params[:venuetypes]).with_area(params[:areas]).joins(:reviews).order("reviews.rating DESC")
    else
      @venues = Venue.all
    end
  end

This gives this kind of a result:

• If venue 1 has a 5 star review it shows the venue partial at the top of the list.

• If venue 2 has a 5 star review and a 1 star review it shows two partials, one at the top and one
  at the bottom of the list.

• If venue 3 has a 5 star review, a 3 star review and a 1 star review it shows three partials, one at the top, one in the middle and one at the bottom of the list.

I just want one partial showing per venue but positioned in the list by the average rating, I feel theres a .average or something missing somewhere how can I acheive this?

Thanks for any help its much appreciated!

edit

Venue model

class Venue < ActiveRecord::Base
  attr_accessible :name, :addressline1, :addressline2, :addressline3, :addressline4, :postcode, :phonenumber, :about, :icontoppx, :iconleftpx, :area_id, :venuetype_id, :lat, :long, :venuephotos_attributes
  belongs_to :area
  belongs_to :venuetype
  has_many :reviews
  has_many :venuephotos

  accepts_nested_attributes_for :venuephotos, :allow_destroy => true

  scope :with_type, lambda { |types|
    types.present? ? where(:venuetype_id => types) : scoped }

  scope :with_area, lambda { |areas|
    areas.present? ? where(:area_id => areas) : scoped }

  def to_param
    "#{id}-#{name.gsub(/\W/, '-').downcase}"
  end

  def add_rating(rating_opts)
    @venue.add_rating(:rating => rating, :reviewer => params[:rating][:reviewer])
    self.reviews.create(rating_opts)
    self.update_rating!
  end

  def update_rating!
    s = self.reviews.sum(:rating)
    c = self.reviews.count
    self.update_attribute(:average_rating, s.to_f / c.to_f)
    self.save(:validate => false)
  end
end

Development log for adding a review

Started POST "/venues/44-rating-test-5/reviews" for 127.0.0.1 at 2011-05-18 09:24:24 +0100
  Processing by ReviewsController#create as JS
  Parameters: {"utf8"=>"✓", "authenticity_token"=>"GZWd67b5ocJOjwKI6z9nJInBXxvQahHrjUtUpdm9oJE=", "review"=>{"rating"=>"5", "title"=>"5 star review"}, "venue_id"=>"44-rating-test-5"}
  [1m[36mVenue Load (1.0ms)[0m  [1mSELECT `venues`.* FROM `venues` WHERE (`venues`.`id` = 44) LIMIT 1[0m
  [1m[35mUser Load (0.0ms)[0m  SELECT `users`.* FROM `users` WHERE (`users`.`id` = 3) LIMIT 1
  [1m[36mSQL (0.0ms)[0m  [1mBEGIN[0m
  [1m[35mSQL (2.0ms)[0m  describe `reviews`
  [1m[36mAREL (0.0ms)[0m  [1mINSERT INTO `reviews` (`title`, `created_at`, `updated_at`, `venue_id`, `user_id`, `rating`) VALUES ('5 star review', '2011-05-18 08:24:24', '2011-05-18 08:24:24', NULL, 3, 5)[0m
  [1m[35mSQL (27.0ms)[0m  COMMIT
  [1m[36mSQL (0.0ms)[0m  [1mBEGIN[0m
  [1m[35mAREL (0.0ms)[0m  UPDATE `reviews` SET `venue_id` = 44, `updated_at` = '2011-05-18 08:24:24' WHERE (`reviews`.`id` = 90)
  [1m[36mSQL (23.0ms)[0m  [1mCOMMIT[0m
  [1m[35mSQL (1.0ms)[0m  SELECT COUNT(*) FROM `reviews` WHERE (`reviews`.venue_id = 44)
  [1m[36mUser Load (0.0ms)[0m  [1mSELECT `users`.* FROM `users` WHERE (`users`.`id` = 3) LIMIT 1[0m
Rendered reviews/_review.html.erb (9.0ms)
Rendered reviews/create.js.erb (22.0ms)
Completed 200 OK in 220ms (Views: 56.0ms | ActiveRecord: 54.0ms)

edit create review method (reviews controller)

def create
    @review = current_user.reviews.create!(params[:review])
    @review.venue = @venue
    if @review.save
      flash[:notice] = 'Thank you for reviewing this venue!'
      respond_to do |format|
        format.html { redirect_to venue_path(@venue) }
        format.js
      end
    else
      render :action => :new
    end
  end

Answer 1

To add to NoICE's answer, by hooking into the :after_add and :after_remove association callbacks, you don't have to remember to call a special add_rating method.

class Venue < ActiveRecord::Base
  has_many :reviews, :after_add => :update_average_rating, :after_remove => :update_average_rating

  def update_average_rating(review=nil)
    s = self.reviews.sum(:rating)
    c = self.reviews.count
    self.update_attribute(:average_rating, c == 0 ? 0.0 : s / c.to_f)
  end

end

Also, you'll want to check count for 0 to prevent division by zero.

When you create the review, you must append with << or concat it to the venue object's reviews association so that the callback gets triggered. For example, this will associate the review to the venue, create the review (INSERT into the db), and trigger the callback:

@venue = Venue.find(params[:venue_id])
@venue.reviews << Review.new(params[:review])

This will create the review but won't trigger the callback, even if the venue_id is the params:

Review.create(params[:review])

If you really want to get your action to trigger the callback, you can change your code to:

def create
  @review = Review.new(params[:review].merge({ :user => current_user, :venue => @venue })
  if @review.valid? and @venue.reviews << @review
  ...

To fix it expediently, though, you could just add @review.venue.update_average_rating just before the line w/ flash[:notice].

Answer 2

If I understand correctly, you have model venue, which have has_many :reviews, and each review has collumn "rating".

I'm oferring alternative code for example given by Michael which should be a LOT MORE faster and millions-of-records ready, but it requires some processing then review is added (covered in this example), which give you huge performance boost when the records are selected, ordered and shown:

Create a migration which adds average_rating as float:

add_collumn :venues, :average_rating, :float, :default => 0.0, :null => false
add_index :venues, :average_rating

Now, in your controller:

# perhaps add paginate at the end instead of .all ...
@venues = Venue.with_type(params[:venuetypes]).with_area(params[:areas]).order("average_rating DESC").all

The model updated:

class Venue < ActiveRecord::Base
  has_many :reviews

  # you'll need to create ratings for this venue via this method, so everything is atomic
  # and transaction safe
  # parameter is hash, so you can pass as many review parameters as you wish, e.g.
  # @venue.add_rating(:rating => rating, :reviewer => params[:rating][:reviewer])
  # or
  # @venue.add_rating(params[:rating])
  # :)
  def add_rating(rating_opts)
    # you can of course add as 
    self.reviews.create(rating_opts)
    self.update_rating!
  end

  # let's update average rating of this venue
  def update_rating!
    s = self.reviews.sum(:rating)
    c = self.reviews.count
    self.average_rating = s.to_f / c.to_f
    self.save(:validate => false)
    # or you can use .update_attribute(:average_rating, s.to_f / c.to_f)
  end

end

Hope this helps. Please ask if you have any questions.

Regards, NoICE