Rails - Oct 2008 - A little help cleaning up SQL query.

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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---