sql - 使用 Rails 'contains operator'(或替代方案)搜索多个值

Question

我有两个模型:Author 和 Book，其中

class Book< ApplicationRecord
 belongs_to :author
end
class Author< ApplicationRecord
  has_many :books
 end

作者有一个name:string

图书有 title:string 和 author_id 作为索引

我有兴趣找到所有拥有包含某些给定字符串的书籍的作者

例如，如果术语是 ['wizard', 'onehundred', 'two cities'] 我想返回其书籍包含标题中术语的作者(一本书名包含 'wizard' 和一本书包含 '一百个和...)

我可以用类似的东西

class Post
 scope :including_all_title, -> (titles) { where(matching_tag_query(titles, 'AND')) }

 private

  def matching_tag_query(titles, condition_separator = 'AND')
    titles.map { |t| "(title LIKE '%#{t}%')" }.join(" #{condition_separator} ")
 end
 end

( Author.joins(:books).merge(Books.including_all_title... )但随着术语数量的增加，情况似乎不太好。

我见过包含 Postgres 的“@>”(例如 Postgres: check if array field contains value?)的响应

但到目前为止我无法让它工作(可能语法不好，我没有收到错误但没有结果)

我想知道如何使用 Active Record(或比我自己的更好的解决方案)实现这一点

编辑:我意识到我可能不清楚，我想做的是:如果 Author(name:'John') 有书 Book(title: 'The wonderful Wizard of Oz')书籍(标题:'神奇动物')Book(title: 'Some other work')Book(title: 'Another book') 我通过 ["%wizard%", "%fantastic%"] 查询我想找到作者的书，匹配关键字的 ALL，所以在这种情况下 John 将是一个匹配项(因为“wizard”匹配一本书，而“fantastic”匹配另一本书，即使有些是不匹配的 - 我对我的感兴趣关键词)

Answer 1

这应该有效:

def matching_tag_query(titles, condition_separator = 'AND')
  titles.map { |t| "(title LIKE '%#{t}%')" }.join(" #{condition_separator} ")
end

但它受到 SQL 注入(inject)的影响，您可以使用 PostgreSQL 的 expr op any(array) and expr op all(array) 使其更清晰使用 ILIKE 运算符。更像这样:

scope :including_all_title, -> (titles) { where('title ilike all(array[?])', titles.map { |t| "%#{sanitize_sql_like(t)}%" })
scope :including_any_title, -> (titles) { where('title ilike any(array[?])', titles.map { |t| "%#{sanitize_sql_like(t)}%" })

对于现实的 titles 应该没问题，但是如果有人向您抛出几千个 titles 那么它可能会遇到查询大小限制并且无论如何都找不到任何东西。

---

回应评论:如果你想找到与任何关键字匹配的所有书籍，那么:

books = Book.where('title like any(array[?])', titles.map { |t| "%#{sanitize_sql_likt(t)}%" })

然后找到作者:

Author.where(id: books.select(:author_id))

这将执行这样的子查询:

select *
from authors
where id in (select author_id from books where ...)

虽然效率不高，但应该足够好了。如果太慢，请将其转换为 JOIN。