Working with Postgresql generate_series in Rails and ActiveRecord

ActiveRecord provides a lot of advantages as an ORM dealing with queries alongwith complex business logics. However things get sometimes difficult with complex queries.

Recently I faced a similar issue, when I had to use generate_series over one of the table which contained date ranges and a value, to get a grouped date by value, the raw sql of which would go something like:


select sum(amount) as sum_amount, custom_date
from my_table,
generate_series(my_table.start_date, my_table.end_date, interval '1 day') custom_date
group by custom_date;

The above looks out of way to write with ActiveRecord as it performs a cross join of original table with generate_series() and none of the AR methods seamlessly allow you to do so.

The other option was writing raw sql, which I wanted to avoid completely as it would take out one of key feature of AR i.e. chaining of conditions, which was must as the code involved a lot of where conditions over the parameters to build the final query.

I worked back to how some of the AR method works, and played with joins() to get desired sql being built successfully as (note the comma in the joins method to get the desired sql 🙂 ):

MyModel.
  joins(", generate_series(my_table.start_date,my_table.end_date,interval '1 day') custom_date").
  group("custom_date").
  select("sum(amount) as sum_amount, custom_date ")

 

The above query became very helpful in writing complex SQLs involving date ranges using Rails!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s