Rails - find_by_sql with heredoc raises error

Question

I have this simple SQL in a heredoc

sql = <<-SQL
  SELECT SUM(price) as total_price, 
    SUM(distance) as total_distance, 
    TO_CHAR(date, 'YYYY-MM') as month
  FROM Rides
  WHERE user_id = #{current_user.id}
  GROUP_BY month
SQL

and a find_by_sql(sql) call

Ride.find_by_sql(sql).each do |row|
  "#{row.month}: { total_distance: #{row.total_distance}, total_price: #{row.total_price} }" 
end

and it raises error:

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "GROUP_BY"
LINE 6:         GROUP_BY month
                ^
:         SELECT SUM(price) as total_price, 
          SUM(distance) as total_distance, 
          TO_CHAR(date, 'YYYY-MM') as month
        FROM Rides
        WHERE user_id = 1
        GROUP_BY month

As you can see, it interpolates the user_id fine so the issue is not in interpolation.

It works if I assign this SQL to a variable as as string, like so:

str = "select sum(distance) as total_distance, sum(price) as total_price, to_char(date, 'YYYY-MM') as month from rides where user_id = #{ current_user.id } group by month"

What is the problem with heredoc?

Answer 1

SQL has a GROUP BY clause not GROUP_BY.

sql = <<-SQL
  SELECT SUM(price) as total_price, 
    SUM(distance) as total_distance, 
    TO_CHAR(date, 'YYYY-MM') as month
  FROM Rides
  WHERE user_id = #{current_user.id}
  GROUP BY month
SQL