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 W. [email protected] 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).