mysql - 具有可变参数的 Ecto 查询和自定义 MySQL 函数

Question

我想执行如下查询:

SELECT id, name
FROM mytable
ORDER BY FIELD(name, 'B', 'A', 'D', 'E', 'C')

FIELD是 MySQL 特定的函数，'B'、'A'、'D'、'E'、'C' 是来自列表的值。

我尝试使用 fragment ，但它似乎不允许仅在运行时才知道的动态元数。

除了使用 Ecto.Adapters.SQL.query 进行全原始处理外，有没有办法使用 Ecto 的查询 DSL 来处理这个问题？

编辑:这是第一种天真的方法，当然行不通:

ids = [2, 1, 3] # this list is of course created dynamically and does not always have three items
query = MyModel
        |> where([a], a.id in ^ids)
        |> order_by(fragment("FIELD(id, ?)", ^ids))

Answer 1

ORM 很棒，直到它们 leak .最终，所有人都会。 Ecto 还很年轻(例如，它只获得了 OR where 子句在一起的能力 30 days ago )，所以它还不够成熟，无法开发出考虑高级 SQL 循环的 API。

调查可能的选项，您并不是唯一一个提出请求的人。 Ecto issue #1485 中提到了无法理解片段中的列表(无论是作为 order_by 或 where 还是任何其他地方的一部分) , 关于 StackOverflow , 在 Elixir Forum 上还有这个blog post .后者特别有启发性。稍后会详细介绍。首先，让我们尝试一些实验。

实验 #1: 可能首先尝试使用 Kernel.apply/3 将列表传递给 fragment，但这不会工作:

|> order_by(Kernel.apply(Ecto.Query.Builder, :fragment, ^ids))

实验 #2: 那么也许我们可以用字符串操作来构建它。为 fragment 提供一个运行时内置的字符串，其中包含足够的占位符以供它从列表中提取:

|> order_by(fragment(Enum.join(["FIELD(id,", Enum.join(Enum.map(ids, fn _ -> "?" end), ","), ")"], ""), ^ids))

给定 ids = [1, 2, 3] 会产生 FIELD(id,?,?,?)。不，这也不起作用。

实验#3:根据 ID 创建完整的最终 SQL，将原始 ID 值直接放入组合字符串中。除了可怕之外，它也不起作用:

|> order_by(fragment(Enum.join(["FIELD(id,", Enum.join(^ids, ","), ")"], "")))

实验#4:这让我想起了我提到的那篇博文。在其中，作者使用一组基于条件数量的预定义宏来解决 or_where 的不足:

defp orderby_fragment(query, [v1]) do
  from u in query, order_by: fragment("FIELD(id,?)", ^v1)
end
defp orderby_fragment(query, [v1,v2]) do
  from u in query, order_by: fragment("FIELD(id,?,?)", ^v1, ^v2)
end
defp orderby_fragment(query, [v1,v2,v3]) do
  from u in query, order_by: fragment("FIELD(id,?,?,?)", ^v1, ^v2, ^v3)
end
defp orderby_fragment(query, [v1,v2,v3,v4]) do
  from u in query, order_by: fragment("FIELD(id,?,?,?)", ^v1, ^v2, ^v3, ^v4)
end

虽然这可以说是有效的并且使用了 ORM，可以这么说，但它要求您有有限的、可管理的可用字段数量。这可能会或可能不会改变游戏规则。

---

我的建议:不要试图解决 ORM 的漏洞。你知道最好的查询。如果 ORM 不接受它，直接用原始 SQL 编写，并记录 ORM 不工作的原因。将它隐藏在函数或模块之后，这样您就可以保留将来更改其实现的权利。有一天，当 ORM catch 来时，您可以很好地重写它，而不会影响系统的其余部分。