postgresql - Phoenix/Ecto - 查询字符串数组中的匹配项

Question

在我的 Phoenix 应用程序的一个表中，我有一个字符串数组字段。我希望能够使用 where: like() 类型的查询来查看数组中的任何值是否包含查询字符串 - 但是，我不确定该怎么做.在应用程序的前一次迭代中，有问题的字段只是一个字符串字段，下面的查询完美运行:

results = from(u in User,
    where: like(u.fulltext, ^("%#{search_string}%"))
    |> Repo.all()

现在我已经将 fulltext 字段更改为一个字符串数组(character varying(255)[]，在 Postgres 术语中)，这个查询可以理解地失败了错误

ERROR 42883 (undefined_function): operator does not exist: character varying[] ~~ unknown

但我不确定如何优化查询以匹配新架构。

例如，用户的fulltext 字段看起来像

["john smith", "john@test.com"]

当search_string为"john"或"@test"或"n smith"时，应返回相关记录 等 - 如果 search_string 匹配任一列表值的任何部分。

用简单的英语来说，查询将读作类似“返回记录，其中在列表 u.fulltext 中找到了像 search_string 这样的值”。

我可以想到各种“hacky”解决方法，比如只返回所有用户的列表，然后使用一些链式 Enum.map 函数来运行它们并检查 fulltext 的值 用于部分匹配，但如果有使用 Ecto 查询语法的更优雅的解决方案，我宁愿选择它。谁能提供任何指导？

Answer 1

您可以在 PostgreSQL 中使用 unnest 和子查询来检查数组中的任何项目是否 LIKE something:

from(p in Post, select: p.tags, where: fragment("exists (select * from unnest(?) tag where tag like ?)", p.tags, "%o%")

在您的情况下，这应该可行:

from(u in User, where: fragment("exists (select * from unnest(?) tag where tag like ?)", u.fulltext, ^("%#{search_string}%"))

iex(1)> Repo.insert! %Post{tags: ~w(foo bar baz)}                                                                              [debug] QUERY OK db=0.3ms
iex(2)> Repo.insert! %Post{tags: ~w(quux)}
iex(3)> Repo.insert! %Post{tags: ~w(hello world)}
iex(4)> query = "%o%"
"%o%"
iex(5)> Repo.all from(p in Post, select: p.tags, where: fragment("exists (select * from unnest(?) tag where tag like ?)", p.tags, "%o%"))
[debug] QUERY OK source="posts" db=3.9ms
SELECT p0."tags" FROM "posts" AS p0 WHERE (exists (select * from unnest(p0."tags") tag where tag like '%o%')) []
[["foo", "bar", "baz"], ["hello", "world"]]