A little help cleaning up SQL query


#1

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:

mysql> desc items;
±-----------±-------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-----------±-------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(255) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
±-----------±-------------±-----±----±--------±---------------+

mysql> desc votes;
±-----------±---------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-----------±---------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| winner_id | int(11) | YES | | NULL | |
| loser_id | int(11) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
±-----------±---------±-----±----±--------±---------------+

Thanks very much for any help that can be offered.

Jack