Elixir中使用Enum.reduce拼接动态SQL WHERE子句的优化方案咨询
动态WHERE条件优化
你当前的字符串拼接写法存在两个核心问题:
- 直接将用户输入的参数插值到SQL字符串中,存在严重的SQL注入风险
- 硬写
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
相关产品推荐
相关产品推荐

