0

I'm setting up a simple ruby/rails app where users can review albums. On an album's show page I average all the user reviews associated with that album through this code in my albums controller 

def show
  @album = Album.find_by_id(params[:id])
  if @album.reviews.present?
    @ratings = Review.where(album_id: @album).average(:rating).truncate(2)
  else
    render 'show'
  end
end

This gives me an average rating for each album. On my home page (routed through a different controller) I want to display the top 7 albums with the highest average rating.

What I originally did was put this code into the separate home page controller: 

@albums = Album.all
@ratings = @albums.each {|album| album.reviews.average(:rating).to_f}
@ranked_ratings =  @ratings.sort_by {|rating| rating}
@top_seven = @ranked_ratings.reverse[0...7]

I thought I found the solution until I realized all I'm showing is the last 7 albums entered into the database.

Going back to the drawing board I have been able to get an array of all the albums (each element within the array is a list of reviews associated with that album) with this code in my controller:

@albums = Album.all
@ratings = @albums.collect {|album| album.reviews}

I'm stuck at this point trying to figure out how to cycle through @ratings and find the average rating for each album_id, then order those records by the highest average rating and display them in a view.

tereško
  • 58,060
  • 25
  • 98
  • 150
Reuben
  • 701
  • 1
  • 9
  • 17

1 Answers1

1

try @albums = Album.joins(:reviews).select("album.id, avg(reviews.rating) as average_rating).group("album.id").order("average_rating DESC")

I extrapolated it from: Ruby on Rails: Order users based on average ratings with most reviews?

Update:

try this instead: @albums = Album.joins(:reviews).select("*, avg(reviews.rating) as average_rating").group("albums.id").order("average_rating DESC")

Community
  • 1
  • 1
moosilauke18
  • 921
  • 3
  • 8
  • 26
  • Yeah, this is the kind of stuff databases are made for. Probably best to let the SQL handle it and not sort & collect in Ruby. – Helios de Guerra Dec 31 '13 at 06:54
  • I'm getting a lot of syntax errors with your suggested solution, trying to refactor right now, should avg be capitalized? – Reuben Dec 31 '13 at 07:17
  • Try changing avg to average than. – moosilauke18 Dec 31 '13 at 07:20
  • I changed it to this `@albums = Album.joins(:reviews).select("album.id", average(reviews.rating), as: average_rating).group("album.id").order("average_rating DESC")` but I'm getting a undefined local variable or method `reviews' error – Reuben Dec 31 '13 at 07:25
  • Or like you said capitalize to AVG. – moosilauke18 Dec 31 '13 at 07:26
  • The quotations in the select should be `.select("...")` so that they are at the beginning and end of the select statement. – moosilauke18 Dec 31 '13 at 07:31