时间戳

首页 > 解决方案 > 在 Elixir 中更好地使用 Enum.reduce

elixir - 在 Elixir 中更好地使用 Enum.reduce

问题描述

我正在尝试以这种方式在 where =1=1 之后创建查询的 where 子句

Enum.reduce(params, "WHERE 1=1 ", fn param, condition = _acc ->
  {name, value} = param
  cond do
    name == "camera_name" && value != "" -> condition <> "AND LOWER(CAMERA_NAME) ilike lower('%#{value}%')"
    name == "requester_name" && value != "" -> condition <> "AND LOWER(REQUESTER_NAME) ilike lower('%#{value}%')"
    true -> condition
  end
end)

参数的值可以是这样的

%{"camera_name" => "gpo", "requester_name" => "junaid"}

上述结果导致

"WHERE 1=1 AND LOWER(CAMERA_NAME) ilike lower('%ju%')"

我认为可能有更好的方法来做到这一点？我也正在附加WHERE 1=1，因为我想附加AND它，如果你能提出一些好的建议，那么请做。

更新：

这是我在 Ecto 助手中找不到任何方法的查询。

  WITH CORE AS (
    SELECT
      'ARCHIVES' AS TAB_NAME,
      ARC.TITLE,
      ARC.EXID,
      ARC.CREATED_AT,
      ARC.FROM_DATE,
      ARC.TO_DATE,
      NULL AS EMBED_CODE,
      ARC.FILE_NAME,
      ARC.FRAMES,
      ARC.URL,
      ARC.PUBLIC,
      ARC.STATUS,
      ARC.TYPE,
      NULL AS EXTRA,
      ARC.REQUESTED_BY,
      ARC.CAMERA_ID
    FROM
      PUBLIC.ARCHIVES AS ARC
    UNION ALL
    SELECT
      'TIMELAPSES',
      TL.TITLE,
      TL.EXID,
      TL.INSERTED_AT,
      TL.FROM_DATETIME,
      TL.TO_DATETIME,
      NULL AS EMBED_CODE,
      NULL AS FILE_NAME,
      NULL AS FRAMES,
      NULL AS URL,
      NULL AS PUBLIC,
      TL.STATUS,
      NULL AS TYPE,
      TL.EXTRA,
      TL.USER_ID,
      TL.CAMERA_ID
    FROM
      PUBLIC.TIMELAPSES AS TL
    UNION ALL
    SELECT
      'COMPARES',
      COMP.EXID,
      COMP.NAME,
      COMP.INSERTED_AT,
      COMP.BEFORE_DATE,
      COMP.AFTER_DATE,
      COMP.EMBED_CODE,
      NULL AS FILE_NAME,
      NULL AS FRAMES,
      NULL AS URL,
      COMP.PUBLIC,
      COMP.STATUS,
      NULL AS TYPE,
      NULL AS EXTRA,
      COMP.REQUESTED_BY,
      COMP.CAMERA_ID
    FROM
      PUBLIC.COMPARES AS COMP ),
    MORE_INFO AS (
      SELECT
        C.*,
        TRIM(CONCAT(U.FIRSTNAME, ' ', U.LASTNAME)) AS REQUESTER_NAME,
        U.EMAIL AS REQUESTER_EMAIL,
        CAM.EXID AS CAMERA_EXID,
        CAM.NAME AS CAMERA_NAME
      FROM
        CORE C
      JOIN USERS U ON
        C.REQUESTED_BY = U.ID
      JOIN CAMERAS CAM ON
        C.CAMERA_ID = CAM.ID )
      SELECT
        *
      FROM
        MORE_INFO

标签： elixir

解决方案

Ecto 非常适合这类事情，尤其是在保护查询免受注入方面。你可以传递一个查询并添加到它上面，所以它是一个很好的累加器。

由于您的问题连接了一个字符串，因此我会坚持下去。我已经使用逻辑处理键对映射进行了迭代，并且效果很好（尽管匹配可能比条件更合适）。

但有时我更喜欢这样的东西管道。这个例子不是累积一个字符串，而是使用一个列表来更容易摆脱你的WHERE 1=1.

def construct_query(params) do
  []
  |> where_not_empty(params["camera_name"], &"LOWER(CAMERA_NAME) ilike lower('%#{&1}%')")
  |> where_not_empty(params["requester_name"], &"LOWER(REQUESTER_NAME) ilike lower('%#{&1}%')")
  |> to_query()
end

defp where_not_empty(conditions, param, fun) when byte_size(param) > 0 do
  [fun.(param) | conditions]
end

defp where_not_empty(conditions, _param, _fun) do
  conditions
end

defp to_query([]) do
  ""
end

defp to_query(conditions) do
  "WHERE " <> Enum.join(conditions, " AND ")
end

construct_query(%{})
#=> ""
construct_query(%{"camera_name" => "gpo"})
#=> "WHERE LOWER(CAMERA_NAME) ilike lower('%gpo%')"
construct_query(%{"camera_name" => "gpo", "requester_name" => "junaid"})
#=> "WHERE LOWER(REQUESTER_NAME) ilike lower('%junaid%') AND LOWER(CAMERA_NAME) ilike lower('%gpo%')"
construct_query(%{"requester_name" => "junaid"})
#=> "WHERE LOWER(REQUESTER_NAME) ilike lower('%junaid%')"

推荐阅读