I have the SQL a for my query, its a little messy with several joins.
Could someone please help/suggest how to turn this into a nice
activerecord query (if possible) so I a) don't have this sql knocking
around in my app breaking when I change database systems and b) use it
a named scope. The query is
"SELECT a.*, b.total_losses
FROM (SELECT items.*, COUNT(votes.id) total_wins FROM items LEFT JOIN
votes ON items.id = votes.winner_id GROUP BY items.id ) a
JOIN (SELECT items.id, COUNT(votes.id) total_losses FROM items
LEFT JOIN votes ON items.id = votes.loser_id GROUP BY items.id ) b ON
order by a.total_wins/b.total_losses DESC;"
my models look like (in case the info helps)
class Item < ActiveRecord::Base
has_many :winners, :class_name => "Vote" ,:foreign_key =>
has_many :losers , :class_name => "Vote" ,:foreign_key =>
class Vote < ActiveRecord::Base
belongs_to :winner, :class_name => "Item"
belongs_to :loser, :class_name => "Item"
and the tables look like: