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 a.id=b.id
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 => "winner_id" has_many :losers , :class_name => "Vote" ,:foreign_key => "loser_id" end
class Vote < ActiveRecord::Base belongs_to :winner, :class_name => "Item" belongs_to :loser, :class_name => "Item" end
and the tables look like:
