SQL Query converting to Rails Active Record Query Interface

Question

I have been using sql queries in my rails code which needs to be transitioned to Active Record Query. I haven't used Active Record before so i tried going through http://guides.rubyonrails.org/active_record_querying.html to get the proper syntax to be able to switch to this method of getting the data. I am able to convert the simple queries into this format but there are other complex queries like

SELECT b.owner, 
       Sum(a.idle_total), 
       Sum(a.idle_monthly_usage) 
FROM   market_place_idle_hosts_summaries a, 
       (SELECT DISTINCT owner, 
                        hostclass, 
                        week_number 
        FROM   market_place_idle_hosts_details 
        WHERE  week_number = '#{week_num}' 
               AND Year(updated_at) = '#{year_num}') b 
WHERE  a.hostclass = b.hostclass 
       AND a.week_number = b.week_number 
       AND Year(updated_at) = '#{year_num}' 
GROUP  BY b.owner 
ORDER  BY Sum(a.idle_monthly_usage) DESC 

which i need in Active Record format but because of the complexity I am stuck as to how to proceed with the conversion.

The output of the query is something like this

+----------+-------------------+---------------------------+
| owner    | sum(a.idle_total) | sum(a.idle_monthly_usage) |
+----------+-------------------+---------------------------+
| abc      |               485 |         90387.13690185547 |
| xyz      |               815 |         66242.01857376099 |
| qwe      |               122 |        11730.609939575195 |
| asd      |                80 |         9543.170425415039 |
| zxc      |                87 |         8027.090087890625 |
| dfg      |                67 |         7303.070011138916 |
| wqer     |                76 |         5234.969814300537 |

Answer 1

Instead of converting it to an active record, you can use the find_by_sql method. Since your query is a bit complex.

You can use also use ActiveRecord::Base.connection, directly to fetch the records.

like this,

ActiveRecord::Base.connection.execute("your query")