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!