Rails - Aug 2006 - calculating average ratings and other imponderables

Okay, this *should* be easy, but it isn''t, although I reserve the right
to
smack myself upside the head if it is:

Assume that a smart has_many ratings, and a rating belongs_to a smart (the 
models are fine).  Each rating has a column smart_id.

Each smart, then, has many ratings, with a column called "rating" that
is
an int (1-10).

I want to find, say, the ten smarts with the highest average rating.

Calculating an average is easy enough--once I know which smart I''m
looking
at.  But how do I find this on the fly?

Anyone?  Anyone?  Bueller?  Bueller?

On 8/3/06, Gary Steven Weisserman <gweiss@umich.edu>
wrote:
> Okay, this *should* be easy, but it isn''t, although I reserve the
right to
> smack myself upside the head if it is:
>
> Assume that a smart has_many ratings, and a rating belongs_to a smart (the
> models are fine).  Each rating has a column smart_id.
>
> Each smart, then, has many ratings, with a column called "rating"
that is
> an int (1-10).
>
> I want to find, say, the ten smarts with the highest average rating.
Not sure if this works (SQL isn''t my strong suit!) but:

select smart_id as id, sum(rating)/count(*) as average from ratings
group by smart_id order by average limit 10;

If not it might point you in the right direction.

Matt

Gary wrote:
> I want to find, say, the ten smarts with the highest average rating.
> 
> Calculating an average is easy enough--once I know which 
> smart I''m looking at.  But how do I find this on the fly?
You have to calculate all averages before you can determine which are
the highest.

Doing it in sql would be fastest, but maybe something like this? (DIRE
WARNING: Utter newbie who hasn''t yet written a Rails app, so take this
as pseudocode as it may not work as is)

def top_ten
  average = Hash.new

  Smart.find(:all).each {|smart|
    r = smart.ratings
    r.inject(0) {|sum, i| sum + i}
    average[smart.id] = sum/r.size
  }

  top = average.keys.sort_by {|x| average[x]}

  # Return top 10 smart ids
  top[-10..top.size]
end

Hopefully this will get you going (and I''d like to know what parts of
the above are wrong for my own learning purposes).

- Mark.