mysql - Rails 4 ActiveRecord 查询 : bind variables in order clause

Question

我正在尝试在我的 mysql 查询中内置一个动态顺序，以便某些匹配项的优先级排在最前面。

Ticket.where(
  "id LIKE :search || name LIKE :search", 
  {search: "%#{params[:search]}]%"}
).order(
  "id = :exact DESC, name = :exact DESC, id LIKE :search DESC, name LIKE :search DESC", 
  {exact: params[:search], search: "%#{params[:search]}%"}
)

假设 params[:search] = 'search_value'，查询应该像这样结束:

SELECT * FROM tickets
WHERE id LIKE "%search_value%" || name LIKE "%search_value%"
ORDER BY id = 'search_value' DESC, name = 'search_value' DESC, id LIKE "%search_value%" DESC, name LIKE "%search_value%" DESC;

无论何时我这样做，我都会从 ActiveRecord::QueryMethods.validate_order_args 得到一个错误，说 Direction should be :asc or :desc.

除了手动清理搜索然后将其直接插入订单字符串之外，有没有办法让它像 where 子句的绑定(bind)变量一样发挥作用？

Answer 1

所以经过一番挖掘，我想我已经找到了解决方案，尽管我希望 Rails 有更好的方法。

我已经将其包含在我的 Rails 项目中

class ActiveRecord::Base
  def self.corder *array
    statement = array.shift
    while (i=array.shift).present?
      case i
      when Hash
        i.each do |k,v|
          statement.gsub!(':' + k.to_s, ActiveRecord::Base.sanitize(v))
        end
      when String
        statement.sub!('?', ActiveRecord::Base.sanitize(i))
      else
        raise "Unknown type: #{i} -- #{i.class}"
      end
    end
    self.order(statement)
  end

  def corder *array
    statement = array.shift
    while (i=array.shift).present?
      case i
      when Hash
        i.each do |k,v|
          statement.gsub!(':' + k.to_s, sanitize(v))
        end
      when String
        statement.sub!('?', sanitize(i))
      else
        raise "Unknown type: #{i} -- #{i.class}"
      end
    end
    self.order!(statement)
  end
end

尽管可以很容易地扩展，但就我的目的而言，它工作得很好。这两个是完全相同的(在类和实例上需要它的简单解决方案)。

此时你可以:

Model.where(
  'id LIKE :search || name LIKE :search', 
  {search: "%#{params[:search]}%"}
).order(
  'id = :exact DESC, name = :exact DESC, id LIKE :rough DESC, name LIKE :rough DESC', 
  {exact: params[:search], rough: "%#{params[:search]}%"}
)

SQL 假设 params[:search] == 'ibm':

SELECT * FROM model WHERE id LIKE '%ibm%' || name LIKE '%ibm%' ORDER BY id = 'ibm' DESC, name = 'ibm' DESC, id LIKE '%ibm%' DESC, name LIKE '%ibm%' DESC;

或者“？”绑定(bind)变量也可以工作，例如:

Model.where(
  'id LIKE ? || name LIKE ?', 
  "%#{params[:search]}%", "%#{params[:search]}%"
).order(
  'id = ? DESC, name = ? DESC, id LIKE ? DESC, name LIKE ? DESC', 
  params[:search], params[:search], "%#{params[:search]}%", "%#{params[:search]}%"
)