Rails, ActiveRecord and SubQueries

Question

I have a postgresql database with hourly gas consumption entries. Now, I need to find the days with the highest consumption for every month.

In plain SQL I'd use subqueries like this:

SELECT 
    DATE_TRUNC('month', day) AS month,
    MAX(dailyconsumption) as maxconsumption
FROM (
    SELECT 
        DATE_TRUNC('day', date) AS day,
        SUM(consumption) AS dailyconsumption
    FROM Records
    GROUP BY day
) t
GROUP BY month

However, I don't know the best way (or any way) to do this in rails. I appreciate any input. Should I bypass ActiveRecord? Performance is a high priority.

Thank You!

Answer 1

Your subquery should be recreated with something like:

subquery = Records
  .all
  .select("DATE_TRUNC('day', date) AS day", "SUM(consumption) AS dailyconsumption")
  .group("day")

To query from a subquery, instead of a table, you use from.

Records.select(...).from(subquery)

Note that subquery is an ActiveRecord relation. Not actual data. It gets converted to SQL in the second statement and the whole thing is run on the DB side.