RAILS/POSTGRESQL 中的多个 LIKE 和 AND 运算符 [英] Multiple LIKE and AND operators in RAILS/POSTGRESQL

问题描述

我使用 Rails 5.0.1 和 Postgresql 作为我的数据库.我有一个包含 :content 列的表格，其中包含单词.

I'm using Rails 5.0.1 and Postgresql as my database. I have a table with column :content which contains words.

问题:当我在寻找一个特定的词时，我想看看这个词是否包含我选择的字母(字符).假设我想 DB 返回包含字母a"、b"和c"的单词(所有这些，但没有特定的顺序)

The problem: When I'm looking for a specific word, I want to see if the word contains letters (chars) of my choice. Let's say i want to DB to return words containg letters "a", "b" and "c" (all of them, but with no specific order)

我在做什么:我发现我可以使用Word.where("content like ?", "%a%").where("content like ?", "%b%").where("content like ?", "%c%")或者Word.where("content like ? content like ? content like ?", "%a%", "%b%", "%c%")在这两种情况下，即使我切换给定字母/子字符串的顺序，它也能正常工作，例如.两者都会找到back"、cab"等词.

What I'm doing: I found that i could use Word.where("content like ?", "%a%").where("content like ?", "%b%").where("content like ?", "%c%") OR Word.where("content like ? content like ? content like ?", "%a%", "%b%", "%c%") In both cases even if i switch order of given letters/substrings it works fine, ex. both would find word "back", "cab" etc..

问题:有没有更好/更DRY的方法来做到这一点?如果想找到有 8 个不同字母的单词怎么办?我是否必须使用内容之类的"?8次?是否可以将参数作为数组传递?(假设我不知道用户将输入多少个字母)

The question: Is there any better/more DRY way to do it? What if want to find word with 8 different letters? Do i have to use "content like ?" 8 times? Is it possible to pass arguments as an array? (let's assume i don't know how many letters user will input)

推荐答案

PostgreSQL 有一个方便的 expr op all (array) 表达式所以你可以这样说:

PostgreSQL has a handy expr op all (array) expression so you can say things like:

where content like all (array['%a%', '%b%', '%c'])

简写为:

where content like '%a%'
  and content like '%b%'
  and content like '%c%'

此外，如果您将一个 Ruby 数组交给 ActiveRecord，它会方便地将 ? 占位符替换为逗号分隔的列表.这让你可以说:

Also, ActiveRecord will conveniently replace a ? placeholder with a comma-delimited list if you hand it a Ruby array. That lets you say things like:

Word.where('content like all (array[?])', %w[a b c].map { |c| "%#{c}%" })

和:

Word.where('content like all (array[?])', some_other_array.map { |c| "%#{c}%" })

这篇关于RAILS/POSTGRESQL 中的多个 LIKE 和 AND 运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！