Q:

Complex SQL converted to active record

I have been puzzling over the following query, and how it could be done using active record.
select * from links where id in 
    (select id from 
        (select votable_id as id, count(votable_id) as count from votes where vote_scope = 'flag' and votable_type = 'Link' group by votable_id ) 
     as x where count < 3);

I am currently using just the raw SQL query in my finder at the moment (find_by_sql), but was wondering if there was a more 'railsy' way of doing this? 
EDIT
Thanks to Joachim Isaksson the query can be squashed to 
select * from links where id in 
    (select votable_id from votes 
     where vote_scope = 'flag' 
     and votable_type = 'Link' 
     group by votable_id 
     HAVING COUNT(*) < 3) ;

A:

Let's start with me not being a rails guru in any capacity, and I can't test run this so a bit "running blind". In other words, take this with a grain of salt :)
Rewriting your query as a join (assuming here your links table has id and one more field called link for the sake of the GROUP BY;
SELECT links.* 
FROM links
JOIN votes
  ON links.id = votes.votable_id
 AND votes.vote_scope = 'flag'
 AND votes.votable_type = 'Link'
GROUP BY votes.votable_id, links.id, links.link
HAVING COUNT(*) < 3;

(SQLfiddle to test with)
...should make something like this work (line split for readability)
Link.joins("JOIN votes ON links.id = votes.votable_id
            AND votes.vote_scope = 'flag' AND votes.votable_type = 'Link'")
    .group("votes.votable_id, links.id, links.link")
    .having("COUNT(*) < 3")

Having your associations set up correctly may allow you to do the join in a more "railsy" way too.

