You need to enable JavaScript to run this app. Elixir中使用Enum.reduce拼接动态SQL WHERE子句的优化方案咨询
优惠活动
大模型
产品
解决方案
定价
更多

Elixir中使用Enum.reduce拼接动态SQL WHERE子句的优化方案咨询

动态WHERE条件优化

你当前的字符串拼接写法存在两个核心问题:

  1. 直接将用户输入的参数插值到SQL字符串中,存在严重的SQL注入风险
  2. 硬写WHERE 1=1属于不必要的hack,Ecto本身的动态查询能力完全可以替代这种写法

推荐用Ecto原生动态查询实现,示例代码如下:

# 先定义参数和过滤逻辑的映射
filter_rules = %{
  "camera_name" => fn value -> 
    dynamic([mi], ilike(fragment("LOWER(?)", mi.camera_name), fragment("LOWER(?)", ^"%#{value}%")))
  end,
  "requester_name" => fn value -> 
    dynamic([mi], ilike(fragment("LOWER(?)", mi.requester_name), fragment("LOWER(?)", ^"%#{value}%")))
  end
}

# 过滤空值参数,生成动态条件列表
dynamic_filters = 
  params
  |> Enum.filter(fn {_key, val} -> not is_nil(val) and val != "" end)
  |> Enum.map(fn {key, val} -> filter_rules[key].(val) end)

# 将条件挂载到基础查询上
base_query = from mi in "more_info", select: mi
final_query = Enum.reduce(dynamic_filters, base_query, fn filter, q -> where(q, ^filter) end)

这种写法所有用户参数都会被Ecto处理为参数化查询,完全避免注入风险,也不需要手动拼接WHERE 1=1:如果没有有效过滤条件,Ecto不会生成WHERE子句,多个条件会自动用AND拼接。

复杂CTE的Ecto实现

Ecto原生支持CTE和UNION操作,你给出的SQL可以完全用Ecto查询构造器实现,不需要手动写SQL字符串:

# 构造CORE CTE的三个UNION ALL子查询
q_archives = from arc in "archives",
  select: %{
    tab_name: "ARCHIVES",
    title: arc.title,
    exid: arc.exid,
    created_at: arc.created_at,
    from_date: arc.from_date,
    to_date: arc.to_date,
    embed_code: type(nil, :string),
    file_name: arc.file_name,
    frames: arc.frames,
    url: arc.url,
    public: arc.public,
    status: arc.status,
    type: arc.type,
    extra: type(nil, :map),
    requested_by: arc.requested_by,
    camera_id: arc.camera_id
  }

q_timelapses = from tl in "timelapses",
  select: %{
    tab_name: "TIMELAPSES",
    title: tl.title,
    exid: tl.exid,
    created_at: tl.inserted_at,
    from_date: tl.from_datetime,
    to_date: tl.to_datetime,
    embed_code: type(nil, :string),
    file_name: type(nil, :string),
    frames: type(nil, :integer),
    url: type(nil, :string),
    public: type(nil, :boolean),
    status: tl.status,
    type: type(nil, :string),
    extra: tl.extra,
    requested_by: tl.user_id,
    camera_id: tl.camera_id
  }

q_compares = from comp in "compares",
  select: %{
    tab_name: "COMPARES",
    title: comp.name,
    exid: comp.exid,
    created_at: comp.inserted_at,
    from_date: comp.before_date,
    to_date: comp.after_date,
    embed_code: comp.embed_code,
    file_name: type(nil, :string),
    frames: type(nil, :integer),
    url: type(nil, :string),
    public: comp.public,
    status: comp.status,
    type: type(nil, :string),
    extra: type(nil, :map),
    requested_by: comp.requested_by,
    camera_id: comp.camera_id
  }

# 合并三个子查询得到CORE CTE
q_core = union_all(q_archives, ^q_timelapses) |> union_all(^q_compares)

# 构造MORE_INFO CTE
q_more_info = from c in "core",
  join: u in "users", on: c.requested_by == u.id,
  join: cam in "cameras", on: c.camera_id == cam.id,
  select: %{
    tab_name: c.tab_name,
    title: c.title,
    exid: c.exid,
    created_at: c.created_at,
    from_date: c.from_date,
    to_date: c.to_date,
    embed_code: c.embed_code,
    file_name: c.file_name,
    frames: c.frames,
    url: c.url,
    public: c.public,
    status: c.status,
    type: c.type,
    extra: c.extra,
    requested_by: c.requested_by,
    camera_id: c.camera_id,
    requester_name: fragment("TRIM(CONCAT(?, ' ', ?))", u.firstname, u.lastname),
    requester_email: u.email,
    camera_exid: cam.exid,
    camera_name: cam.name
  }

# 组装最终查询
base_query = 
  from mi in "more_info", select: mi
  |> with_cte("core", as: ^q_core)
  |> with_cte("more_info", as: ^q_more_info)

组装完成的base_query可以直接和前面的动态过滤逻辑结合,后续要加分页、排序等操作也可以直接用Ecto的对应方法实现,比手动拼接SQL的可维护性高很多。


内容的提问来源于stack exchange,提问作者Junaid Farooq

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.18 06:36:06