CODEWITHSUNDEEP
sqlruby-on-railsactiverecordsubqueryrails-postgresql
potofski
potofski

Reputation: 101

Rails, ActiveRecord and SubQueries

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!

Upvotes: 3

Views: 3351

Answers (2)

Mark Bolusmjak
Mark Bolusmjak

Reputation: 24399

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.

Upvotes: 3

Mario Carrion
Mario Carrion

Reputation: 672

If performance is high priority then consider using a View, a view can be represented in Active Record as regular Model, you could even consider using a Materialized View and periodically update the records, that way you won't need to calculate everything every single time.

See the scenic gem for adding views and materialized views as part of your migrations.

Upvotes: 0

Related Questions