Postgres query works on Postgres client but not on Rails Active Record-CodePudding

I have this query that works fine on my Postgres client, however when I try on Rails I get an error. Here's the query:

sql_query = <<-SQL.squish
  SELECT *
       , total_price - taxes - shipping - total_discount AS net_sales
  FROM (SELECT created_at
             , COALESCE(total_orders, 0)        AS total_orders
             , COALESCE(total_price, 0)         AS total_price
             , COALESCE(taxes, 0)               AS taxes
             , COALESCE(shipping, 0)            AS shipping
             , COALESCE(average_order_value, 0) AS average_order_value
             , COALESCE(total_discount, 0)      AS total_discount
        FROM generate_series(timestamp '2022-07-20'
                 , timestamp '2022-07-26'
                 , interval '1 day') AS g(created_at)
                 LEFT JOIN ( -- ③
            SELECT created_at::date
                 , count(*)            AS total_orders
                 , sum(total_price)    AS total_price
                 , sum(taxes)          AS taxes
                 , sum(shipping)       AS shipping
                 , avg(total_price)    AS average_order_value
                 , sum(total_discount) AS total_discount
            FROM orders
            WHERE shop_id = 43
              AND active                    
              AND created_at >= '2022-07-20'
              AND created_at < '2022-07-27'
            GROUP BY 1) o USING (created_at)
       ) sub
  ORDER BY created_at DESC;
SQL

ActiveRecord::Base.connection.execute(sql_query).values

And this is the error I'm getting:

PG::SyntaxError: ERROR: syntax error at end of input LINE 1: ...P BY 1) o USING (created_at) ) sub ORDER BY created_at DESC; ^

What am I missing here?

CodePudding user response：

The <<-SQL.squish on the start and SQL on the end must be same.

Change the <<-SQL.squish just to <<-SQL.

CodePudding user response：

Please try this

sql_query = <<-SQL
  SELECT *
       , total_price - taxes - shipping - total_discount AS net_sales
  FROM (SELECT created_at
             , COALESCE(total_orders, 0)        AS total_orders
             , COALESCE(total_price, 0)         AS total_price
             , COALESCE(taxes, 0)               AS taxes
             , COALESCE(shipping, 0)            AS shipping
             , COALESCE(average_order_value, 0) AS average_order_value
             , COALESCE(total_discount, 0)      AS total_discount
        FROM generate_series(timestamp '2022-07-20'
                 , timestamp '2022-07-26'
                 , interval '1 day') AS g(created_at)
                 LEFT JOIN ( -- ③
            SELECT created_at::date
                 , count(*)            AS total_orders
                 , sum(total_price)    AS total_price
                 , sum(taxes)          AS taxes
                 , sum(shipping)       AS shipping
                 , avg(total_price)    AS average_order_value
                 , sum(total_discount) AS total_discount
            FROM orders
            WHERE shop_id = 43
              AND active                    
              AND created_at >= '2022-07-20'
              AND created_at < '2022-07-27'
            GROUP BY 1) o USING (created_at)
       ) sub
  ORDER BY created_at DESC;
SQL

ActiveRecord::Base.connection.execute(sql_query).values

You can furthur read at : What are these strings called? What is squish? Ruby