Rails:如何使用逗号分隔的值并构建SQL查询?

Using a Rails 3 Active Record model (postgres db) I want to take a user search input of something like this:

使用Rails 3活动记录模型(postgres db)，我想获取用户搜索输入如下内容:

"some string, some other string, final string"

From there I want to split strings on the commas and build an SQL query that looks something like this

从这里开始，我想在逗号上分割字符串，并构建一个类似于这样的SQL查询

SELECT * FROM items WHERE item_name SIMILAR TO '%(some string)%' OR item_name SIMILAR TO '%(some other string)%' OR item_name SIMILAR TO '%(final string)%'

I'm struggling to come up with a way to build this query as I am rather unfamiliar with the syntax of Ruby.

由于我对Ruby的语法不太熟悉，所以很难找到构建这个查询的方法。

3 个解决方案

#1

I would skip SIMILAR TO and go straight to POSIX regexes, I'm pretty sure SIMILAR TO will be translated to a regex internally so why bother? Also, ~ will let you use ANY to produce a nice readable expression. You could do something like this:

我会跳过类似的内容，直接跳到POSIX regex，我很确定类似的东西会被转换成regex，所以为什么要麻烦呢?此外，~将允许您使用ANY生成一个良好的可读表达式。你可以这样做:

str   = 'some string, some other string, final string'
items = Item.where('item_name ~ any(array[?])', str.split(/\s*,\s*/))

That will end up running SQL like this:

最终会运行这样的SQL:

select "items".* from "items" where item_name ~ any(array['some string', 'some other string', 'final string'])

and that will produce the same results as your SIMILAR TO version without a bunch of string wrangling.

这将产生与您的版本相似的结果，而不会产生大量的字符串争吵。

If you're faced with a CSV string that can contain regex metacharacters then you probably want to throw some escaping in the mix. Backslashing anything that isn't alphanumeric should be safe enough:

如果您遇到一个包含regex元字符的CSV字符串，那么您可能需要在其中加入一些转义。回砍任何不是字母数字的东西应该足够安全:

str   = 'some string, some other string, final string'
pats  = str.split(/\s*,\s*/)
           .map { |s| s.gsub(/\p{^Alnum}/) { '\\' + $& } }
items = Item.where('item_name ~ any(array[?])', pats)

Switching to LIKE is also an option, then you'd only have to worry about _ and %:

切换到LIKE也是一个选项，那么你只需要担心_和%:

str   = 'some string, some other string, final string'
pats  = str.split(/\s*,\s*/)
           .map { |s| s.gsub(/[_%]/, '%' => '\\%', '_' => '\\_') }
           .map { |s| '%' + s + '%' }
items = Item.where('item_name like any(array[?])', pats)

In real life you'd bust the escaping mess (and the "add LIKE percent signs" mess) out into utility methods to make your code cleaner.

在现实生活中，为了使代码更简洁，你可能会把转义的混乱(以及“添加百分比”的混乱)分解到实用工具中。

If you don't care about case then you can use ~* or ilike for case insensitive pattern matching.

如果您不关心大小写，那么您可以使用~*或ilike进行大小写不敏感的模式匹配。

#2

Try this way,

尝试这种方式,

 string = 'some string, some other string, final string'
 patterns = search_string.split(", ").map{|str| "%#{str}%" }
 items = Item.where('item_name like any(array[?])', patterns)

#3

This should work.

这应该工作。

search_string = "some string, some other string, final string"

strings = search_string.split(", ").map{|s| '%' + s + '%'}
instructions = ''
strings.each do |string|
  instructions <<  ' OR ' unless instructions == ''
  instructions <<  'item_name like ?'
end
DatabaseTable.where(instructions, *strings)

#1

str   = 'some string, some other string, final string'
items = Item.where('item_name ~ any(array[?])', str.split(/\s*,\s*/))