CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
Dave Oh
Dave Oh

Reputation: 135

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) ;

Upvotes: 0

Views: 88

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180867

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.

Upvotes: 1

Related Questions