# A little help cleaning up SQL query

**URL:** <https://www.ruby-forum.com/t/a-little-help-cleaning-up-sql-query/151387>\
**Category:** Rails\
**Created:** [October 20, 2008, 10:14pm UTC](https://www.ruby-forum.com/t/a-little-help-cleaning-up-sql-query/151387 "2008-10-20T22:14:29Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\
**Post date:** [October 20, 2008, 10:14pm UTC](https://www.ruby-forum.com/t/a-little-help-cleaning-up-sql-query/151387/1 "2008-10-20T22:14:29Z")

</div>

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