Raw SQL and generate_series in Rails Active Record

Question

Not sure what to name title for this, but I have a difficult time rewriting raw SQL to Rails ActiveRecord methods.

I want to extract sum(total) of transactions by each month in the date range that is chosen.

NOTE: all months that are in that range needs to be shown. For that I'm using generate_series SQL method to generate series of months in that range.

The method I'm writing is in the Query object that has an existing relation that is filtered transactions from before.

My functions for now:

  def group_total_by_months
   @relation.joins("RIGHT JOIN generate_series(TIMESTAMP '1-1-2018', TIMESTAMP '1-1-2020', interval '1 month') AS series
                     ON date_trunc('month', transactions.payment_date) = series")
            .group("series")
            .select("series AS payment_date, sum(transactions.total) AS total")
  end

Result:

#<ActiveRecord::AssociationRelation [#<Transaction id: nil, total: 0.61173e3, payment_date: "2019-06-01 00:00:00">, #<Transaction id: nil, total: 0.364446e4, payment_date: "2019-10-01 00:00:00">, #<Transaction id: nil, total: 0.1625e4, payment_date: "2019-08-01 00:00:00">]>

This is sumed total by months, but only months that exist in transactions. Why? Because generated SQL looks like this and FROM clause is from all transactions, not filtered ones:

    SELECT series AS payment_date, sum(transactions.total) AS total FROM \"transactions\" 
RIGHT JOIN generate_series(TIMESTAMP '1-1-2018', TIMESTAMP '1-1-2020', interval '1 month') AS series\n    ON date_trunc('month', transactions.payment_date) = series 
WHERE \"transactions\".\"account_id\" = 1 
GROUP BY series

I need SQL that looks like this:

WITH filteret_transactions AS (
    SELECT * FROM transactions WHERE transactions.account_id = 1
)

 SELECT series AS payment_date, sum(filteret_transactions.total) AS total 
    FROM  filteret_transactions
  RIGHT JOIN generate_series(TIMESTAMP '1-1-2018', TIMESTAMP '1-1-2020', interval '1 month') AS series
  ON date_trunc('month', filteret_transactions.payment_date) = series GROUP BY series

How can I achive that?

Aarthi · Accepted Answer · 2019-08-25T17:05:19.383

You need to apply the condition before the joining happens. You can pass the needed params as arguments and do the query as you did in the first approach. I have added the condition in the joining itself(you can add more if needed) and used the model itself(Transaction) to joining.

def group_total_by_months(account_id: )
   Transaction.joins("RIGHT JOIN generate_series(TIMESTAMP '1-1-2018', TIMESTAMP '1-1-2020', interval '1 month') AS series
                     ON date_trunc('month', transactions.payment_date) = series AND transactions\".\"account_id\" = #{account_id}")
            .group("series")
            .select("series AS payment_date, sum(transactions.total) AS total")
end

EDIT: possible solution(but won't work with all situations)

You can get the applied where conditions with this method(https://apidock.com/rails/ActiveRecord/Relation/where_values_hash). create a query condition and use it in the raw query.

def group_total_by_months

  applied_conditions = []

  @transation.where_values_hash.each do |p|
    applied_conditions << "AND #{p.first} = #{p.second}"
  end

  Transaction.joins("RIGHT JOIN generate_series(TIMESTAMP '1-1-2018', TIMESTAMP '1-1-2020', interval '1 month') AS series
                         ON date_trunc('month', transactions.payment_date) = series #{applied_conditions.join(' ')}")
                .group("series")
                .select("series AS payment_date, sum(transactions.total) AS total")
 end

NOTE

>> Project.where(id: 12).where_values_hash
>>  {"id"=>12}
>> Project.where('id = 12').where_values_hash
>> {}

Ref: SQL join: where clause vs. on clause

Thanks, @Aarthi, I'm really sad there id no other way to use `@relation`. I thought I don't know enough ActiveRecord and don't know how to do it with existing @relation. — , Aug 25 '19 at 16:35
Ofcourse, this is with date range and input kind, `inputs = GroupedTransactionsQuery.new(relation: current_user.account.transactions.inputs.from_date(@start_date).to_date(@start_date))` and inside query object initalizer: `@relation = params[:relation] || Transaction.all` — , Aug 25 '19 at 16:44
I have updated answer and that is only a possible solution which doesn't work in some cases(added that in the note) — Aarthi, Aug 25 '19 at 17:06

Raw SQL and generate_series in Rails Active Record

1 Answers1