如何用Ecto.Query实现多列组合的模糊姓名查询？

要实现支持全名组合的模糊搜索，核心是利用PostgreSQL的原生字符串拼接能力，而非Elixir的字符串处理函数。以下是两种可行方案：

方案1：直接拼接姓名字段进行匹配 #

通过fragment调用PostgreSQL的concat函数（或||运算符），将first_name和last_name拼接成完整姓名后，再用ilike做模糊匹配。同时保留原有单个字段的匹配逻辑，兼顾单关键词搜索场景：

def search_persons_by_name(name) do
  query = "%#{name}%"

  Person
  # 匹配完整姓名（名+空格+姓）
  |> where([p], ilike(fragment("concat(?, ' ', ?)", p.first_name, p.last_name), ^query))
  # 保留原有单字段匹配逻辑
  |> or_where([p], ilike(p.first_name, ^query))
  |> or_where([p], ilike(p.last_name, ^query))
  |> Repo.all()
end

方案原理 #

Ecto查询表达式需转换为SQL语法，无法直接使用Elixir的字符串插值（如"#{p.first_name} #{p.last_name}"），而fragment允许嵌入PostgreSQL原生函数。concat(?, ' ', ?)在数据库层面完成姓名拼接，ilike实现不区分大小写的模糊匹配。

方案2：拆分搜索关键词进行多条件匹配 #

若需更灵活的搜索（比如支持"smith john"也能匹配John Smith），可拆分搜索字符串为多个关键词，要求每个关键词都能匹配名或姓：

def search_persons_by_name(name) do
  # 拆分搜索词为多个关键词，每个添加模糊匹配通配符
  keywords = name |> String.trim() |> String.split() |> Enum.map(&"%#{&1}%")

  # 遍历关键词，每个关键词需匹配名或姓
  Enum.reduce(keywords, Person, fn keyword, query_acc ->
    query_acc |> where([p], ilike(p.first_name, ^keyword) or ilike(p.last_name, ^keyword))
  end)
  |> Repo.all()
end

方案优势 #

• 支持任意顺序的关键词组合，输入"john smith"或"smith john"都能命中目标记录；
• 天然支持多关键词筛选，比如输入"john s"也能匹配John Smith。

常见错误原因解释 #

1. 直接用Elixir字符串拼接字段报错：Ecto会将查询表达式转换为SQL，"#{p.first_name} #{p.last_name}"是Elixir语法，无法转换为合法SQL，必须用fragment调用数据库拼接函数；
2. 使用Elixir的String.downcase报错：String.downcase是Elixir运行时函数，数据库无法识别。若需统一大小写处理，应使用PostgreSQL的lower函数，示例：

   ilike(lower(p.first_name), ^String.downcase(query))
   

   不过ilike本身已支持不区分大小写匹配，通常无需额外调用lower。

内容的提问来源于stack exchange，提问作者Jethro Cao