Rails ActiveRecord Arels: Unsupported argument type: String. Construct an Arel node instead

Question

I'm using Arels for creating query. In this query, I use generate_series function. Here is my code:

    def generate_series
      Arel::Nodes::NamedFunction.new('GENERATE_SERIES', [start_date, end_day, '1 day'])
    end

def start_date
      Arel::Nodes::SqlLiteral.new(<<-SQL
         CASE WHEN DATE_PART('hour', NOW() AT TIME ZONE 'ICT') < #{Time.now - 3days} THEN (CURRENT_DATE - INTERVAL '14 days') ELSE (CURRENT_DATE - INTERVAL '13 days') END
      SQL
      )
    end

    def end_date
      Arel::Nodes::SqlLiteral.new(<<-SQL
         CASE WHEN DATE_PART('hour', NOW() AT TIME ZONE 'ICT') < #{Time.now} THEN (CURRENT_DATE - INTERVAL '1 day') ELSE CURRENT_DATE END
      SQL
      )
    end

When I try to test by generate_series.to_sql. I meet exception:

Arel::Visitors::UnsupportedVisitError: Unsupported argument type: String. Construct an Arel node instead.

I try to shorter my code for testing:

def generate_series
      Arel::Nodes::NamedFunction.new('GENERATE_SERIES', ['19/11/2012', '20/11/2012', '1 day'])
 end

The problem is same. Please tell me how can I fix this problem.

Answer 1

Your last parameter should be an Arel::Nodes. So you should wrap 1 day into an Arel::Nodes:SqlLiteral. Here is updated code:

def generate_series
      Arel::Nodes::NamedFunction.new('GENERATE_SERIES', 
             [start_date, end_date, Arel::Nodes::SqlLiteral.new('\'1 day\'')]
      )
end

Noted that you must also wrap 1 day inside (single) quotes, i.e: '1 day'. Because if you don't do this, the generated query will be:

GENERATE_SERIES(start, stop, 1 day) 

But true query should be:

GENERATE_SERIES(start, stop, '1 day') 

Answer 2

You can use Arel::Nodes.build_quoted to quote literals:

def generate_series
      Arel::Nodes::NamedFunction.new('GENERATE_SERIES', 
             [start_date, end_date, Arel::Nodes.build_quoted('1 day')]
      )
end

The advantage of this is that you don't have to remember to include and manually escape quotes like you do with Arel::Nodes::SqlLiteral.