将值数组发送到Ruby中的SQL查询？ [英] Sending array of values to a sql query in ruby?

问题描述

我正在努力解决似乎是红宝石语义问题的问题。我正在写一个方法，该方法从表单中获取可变数量的参数并创建一个 Postgresql查询。

I'm struggling on what seems to be a ruby semantics issue. I'm writing a method that takes a variable number of params from a form and creates a Postgresql query.

def self.search(params)
    counter = 0
    query = ""
    params.each do |key,value|
        if key =~ /^field[0-9]+$/
            query << "name LIKE ? OR "
            counter += 1
        end
    end
    query = query[0..-4] #remove extra OR and spacing from last

    params_list = []
    (1..counter).each do |i|
      field = ""
      field << '"%#{params[:field'
      field << i.to_s
      field << ']}%", '
      params_list << field
    end
    last_item = params_list[-1]
    last_item = last_item[0..-3] #remove trailing comma and spacing
    params_list[-1] = last_item

    if params
        joins(:ingredients).where(query, params_list)
    else
        all
    end
end

即使params_list是一个数组，其值在数量上与名称LIKE？匹配查询中的零件，出现错误：以下名称中的绑定变量数量错误（1代表2）：name LIKE吗？还是命名为LIKE？？我尝试使用params_list作为字符串，但效果也不佳。
我是红宝石的新手。

Even though params_list is an array of values that match in number to the "name LIKE ?" parts in query, I'm getting an error: wrong number of bind variables (1 for 2) in: name LIKE ? OR name LIKE ? I tried with params_list as a string and that didn't work any better either. I'm pretty new to ruby.

我使用以下代码处理了2个参数，但是希望允许用户最多提交5个（：field1，，field2，：field3 ... ）

I had this working for 2 params with the following code, but want to allow the user to submit up to 5 ( :field1, :field2, :field3 ...)

def self.search(params)
    if params
        joins(:ingredients).where(['name LIKE ? OR name LIKE ?', 
            "%#{params[:field1]}%", "%#{params[:field2]}%"]).group(:id)
    else
        all
    end
end

有人会掉下吗我应该如何真正编程呢？

Could someone shed some light on how I should really be programming this?

推荐答案

PostgreSQL支持标准的SQL数组和标准的 任何操作（...） 语法：

PostgreSQL supports standard SQL arrays and the standard any op (...) syntax:

9.23.3。 ANY / SOME（数组）

expression operator ANY (array expression)
expression operator SOME (array expression)

右侧是带括号的表达式，必须产生一个数组值。使用给定的 operator 对左侧表达式求值并将其与数组的每个元素进行比较，该运算符必须产生布尔结果。如果获得任何真实结果，则 ANY 的结果为 true。如果找不到正确的结果（包括数组具有零个元素的情况），则结果为 false。

The right-hand side is a parenthesized expression, which must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. The result of ANY is "true" if any true result is obtained. The result is "false" if no true result is found (including the case where the array has zero elements).

您可以像这样构建SQL：

That means that you can build SQL like this:

where name ilike any (array['%Richard%', '%Feynman%'])

这很好而且很简洁，那么我们如何让Rails构建它呢？实际上，这很容易：

That's nice and succinct so how do we get Rails to build this? That's actually pretty easy:

Model.where('name ilike any (array[?])', names.map { |s| "%#{s}%" })

不需要手动引用，ActiveRecord将转换为数组填充到其中的？占位符后，将其显示为正确引用/转义的列表。

No manual quoting needed, ActiveRecord will convert the array to a properly quoted/escaped list when it fills the ? placeholder in.

现在，您只需要构建名称数组。这样的简单操作应该可以完成：

Now you just have to build the names array. Something simple like this should do:

fields = params.keys.select { |k| k.to_s =~ /\Afield\d+\z/ }
names  = params.values_at(*fields).select(&:present)

您还可以将单个'a b'输入转换为'a' ，'b'，方法是将分割和展平扔进混合物：

You could also convert single 'a b' inputs into 'a', 'b' by tossing a split and flatten into the mix:

names = params.values_at(*fields)
              .select(&:present)
              .map(&:split)
              .flatten

这篇关于将值数组发送到Ruby中的SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！