How to prevent added quotes in Order by in Rails-CodePudding

I am attempting to order by a column of type "character varying []" and cast it to an integer[] while sorting (instead of using the default ASCII comparison sort). I am using a Postgresql database.

I've found that the following query works as expected:

select <col> from <table> order by <col>::integer[] desc

Unfortunately, when I attempt to programmatically do this in rails it is adding quotes around the column and casting suffix. This results in it thinking "::integer[]" is part of the column name - and of course there is no such column. Thus the query fails.

Here is the rails code:

scope.order([ '<col>::integer[]', 'desc', 'NULLS LAST' ].join(' '))

And this is the query it produces:

select <col> from <table> order by "<table>"."<col>::integer[]" desc

How can I implement this properly with rails?

CodePudding user response：

Thanks to @engineersmnky comment, I found the solution that I need in my code.

In the code I'm actually processing an array of columns (and directions) to sort by. It turns out the solution was indeed to use the Arel.sql() function to process the order by parameters prior to calling scope.order(), with the end result looking something like this:

def sort(scope, sorts)
  str = \
    sorts.map |sort| do
      col = get_sort_column_alias(sort[0])
      dir = sort[1]
      nullpos = (dir == 'asc') ? 'FIRST' : 'LAST'
      "#{col} #{dir} NULL #{nullpos}"
    end
  scope.order(Arel.sql(str))
end

def get_sort_column_alias(col)
  case col
  when 'target' then 'target::integer[]'
  ...
  else col
  end
end