How to convert a plain sql query with subqueries to use rails active record

Question

I have tried converting this plain sql query to rails active record but I am unable to do so.

select vote_shares.election_year as vs_election_name,
   vote_shares.party as vs_party,
   (sum(vote_shares.party_seats)/totals.total)*100 AS vs
   from pcdemographics INNER JOIN vote_shares on vote_shares.pc_id = pcdemographics.pc_id,
   (
         SELECT vote_shares.election_name, sum(vote_shares.party_seats) as total 
         FROM `pcdemographics` 
         INNER JOIN vote_shares on vote_shares.pc_id = pcdemographics.pc_id 
         GROUP BY `election_name`
   ) AS totals
   where vote_shares.election_name=totals.election_name
   group by vote_shares.party,vote_shares.election_name;

This is what I have tried

@vssubquery = Pcdemographic.select('vote_shares.election_name, sum(vote_shares.party_seats) as total').joins('INNER JOIN vote_shares on vote_shares.pc_id = pcdemographics.pc_id')

Pcdemographic.select("vote_shares.election_year as vs_election_year,
   vote_shares.party as vs_party,
   (sum(vote_shares.party_seats)/'#{totals.total}')*100 AS vs").from(@vssubquery,:totals)
                        .joins("INNER JOIN vote_shares on vote_shares.pc_id = pcdemographics.pc_id and vote_shares.election_name='#{totals.election_name}'")

Can you share any approach you have tried so far? Do you have `ActiveRecord` models like `VoteShare` and `Pcdemographic` in your rails project that can access the data? — dave_slash_null, Aug 09 '17 at 15:44
Yes I have VoteShare and Pcdemographic models . Currently I have a Pcdemographic model that has an inner join on vote share with multiple filter scopes. However now that there is a subquery I am not sure how to access the inner value to perform the computation — coder, Aug 09 '17 at 15:48
I thought of using find_my_sql but then I need have scopes that i need to chain — coder, Aug 09 '17 at 15:51
Is there an error in your first column for `select`? Looks like maybe `vote_shares.election_year` should be `vote_shares.election_name` based on the alias `vs_election_name` and `group by` clause. — dave_slash_null, Aug 09 '17 at 15:57
No i wrote the as statement wrong. I have tested this query in mysql and it returns what i want — coder, Aug 09 '17 at 15:58
The issue is that I need it as an active record relation and not as an array — coder, Aug 09 '17 at 16:05
Looks like you may want to look into the [active record `from` method](http://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-from) for your subquery in conjuction with `joins`. I have never done this personally so I can't give any morespecific advice at this time. — dave_slash_null, Aug 09 '17 at 16:10
If you post the code of your models and what you've tried so far, it will help us give you a better answer. — brainbag, Aug 09 '17 at 16:31
Have you tried this: https://stackoverflow.com/questions/16324305/turn-sql-query-into-activerecord-relation — NM Pennypacker, Aug 09 '17 at 16:37

score 0 · Answer 1 · answered Aug 09 '17 at 17:15

My answer might not be what you hoped for but I recommend not using AR, use Sequel (http://sequel.jeremyevans.net/) instead. It uses the concept of Datasets which I don't think has any equivalent in AR.

Disclaimer: Nobody asked me to advertise for it. I used both AR and Sequel and I found that Sequel is much better to perform complex queries and avoid the N+1 problem.

score 0 · Answer 2 · answered Aug 09 '17 at 18:40

0

Did you try find_by_sql method?

answered Aug 09 '17 at 18:40

lisowski.r

3,671
1
19
16

Find by sql does not return an active record relation . I need it to return a relation so that I can chain scopes – coder Aug 09 '17 at 18:45

How to convert a plain sql query with subqueries to use rails active record

2 Answers2