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 |
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,