rails activerecord'where'搜索数组

Hi I have a problem with search query, with 3 values (val1, val2, tags) from which one is an array(tags). I know that rails doesn`t support arrays but Postgres does, and I have it installed.

嗨,我有一个搜索查询的问题,有3个值(val1,val2,标签),其中一个是数组(标签)。我知道rails不支持数组但Postgres支持数组,我安装了它。

I have a model action:

我有一个模型动作:

  def self.find_object(val1, val2, tags)
    if tags.blank?
      Object.where( val1: val1, val2: val2 ).to_a
    else
      Object.where( "val1 = ? and val2 = ? and tags = [?]", val1, val2, tags).to_a
    end
  end

But I get an error:

但是我收到一个错误:

PG::SyntaxError: ERROR: syntax error at or near "[" LINE 1: ...HERE (val1 = 'Ziemia' and val2 = '1' and tags = ['Jadeit']... ^ : SELECT "objects".* FROM "objects" WHERE (val1 = 'Ziemia' and val2 = '1' and tags = ['Jadeit'])

PG :: SyntaxError:错误:语法错误在“[”第1行:......这里(val1 ='Ziemia'和val2 ='1'和tags = ['Jadeit'] ...... ^:SELECT“对象“。* FROM”对象“WHERE(val1 ='Ziemia'和val2 ='1'和tags = ['Jadeit'])

My problem is that I don't know how to handle array search. In my test app, code like this >> Student.where("hobby && ARRAY[?]", hobby) where hobby was an array works, so this was my start in this example. I will be glad for any hints how to handle this :)

我的问题是我不知道如何处理数组搜索。在我的测试应用程序中,像这样的代码>> Student.where(“hobby && ARRAY [?]”,爱好),其中的业余爱好是一个数组,所以这是我在这个例子中的开始。我会很高兴任何提示如何处理这个:)

Edit: added error message

编辑:添加了错误消息

1 个解决方案

#1

There are 2 syntax to create an array in PostgreSQL:

在PostgreSQL中有2种语法来创建数组:

ARRAY[1,2,3]
'{1,2,3}'

So either of those will work :

所以其中任何一个都可行:

Object.where( "val1 = ? and val2 = ? and tags = ARRAY[?]", val1, val2, tags).to_a
# or
Object.where( "val1 = ? and val2 = ? and tags = '{?}'", val1, val2, tags).to_a

Note however that when using the '{}' syntax PostgreSQL expects strings to be in double quotes (instead of the usual single quotes), and as such using this syntax with string arrays and ActiveRecord will fail. I would suggest you always use the ARRAY[] syntax which should never fail.

但请注意,当使用'{}'语法时,PostgreSQL要求字符串使用双引号(而不是通常的单引号),因此使用字符串数组和ActiveRecord的语法将失败。我建议你总是使用ARRAY []语法,它永远不会失败。

#1