Forum: Ruby on Rails A little help cleaning up SQL query.

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
(Guest)
on 2008-10-21 00:14
(Received via mailing list)
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
This topic is locked and can not be replied to.