具有可变Arity的ecto查询和自定义MySQL函数 [英] Ecto query and custom MySQL function with variable arity

问题描述

我想执行如下查询:

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

FIELD 是一个MySQL特定功能，'B', 'A', 'D', 'E', 'C'是来自列表的值.

我尝试使用片段，但是没有似乎允许仅在运行时才知道动态对等.

I tried using fragment, but it doesn't seem to allow dynamic arity known only in the runtime.

除了使用Ecto.Adapters.SQL.query进行完全原始操作外，还有没有办法使用Ecto的查询DSL处理此问题?

Except going full-raw using Ecto.Adapters.SQL.query, is there a way to handle this using Ecto's query DSL?

这是第一种幼稚的方法，它当然不起作用:

Here's the first, naive approach, which of course does not work:

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))

推荐答案

ORM很棒，直到他们泄漏.最终一切都会做. Ecto还很年轻(例如，它只能在OR其中子句一起 30天前 a>)，因此开发一个考虑高级SQL回旋的API只是不够成熟.

ORM are wonderful, until they leak. All do, eventually. Ecto is young (f.e., it only gained ability to OR where clauses together 30 days ago), so it's simply not mature enough to have developed an API that considers advanced SQL gyrations.

调查可能的选项，在请求中您并不孤单. StackOverflow 在 Elixir论坛和此博客发布.后者特别具有启发性.一点点更多.首先，让我们尝试一些实验.

Surveying possible options, you're not alone in the request. The inability to comprehend lists in fragments (whether as part of order_by or where or any where else) has been mentioned in Ecto issue #1485, on StackOverflow, on the Elixir Forum and this blog post. The later is particulary instructive. More on that in a bit. First, let's try some experiments.

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

Experiment #1: One might first try using Kernel.apply/3 to pass the list to fragment, but that won't work:

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

实验#2:然后，也许我们可以使用字符串操作来构建它.如何给fragment一个在运行时内置的，具有足够占位符的字符串以使其从列表中拉出:

Experiment #2: Then perhaps we can build it with string manipulation. How about giving fragment a string built-at-runtime with enough placeholders for it to pull from the list:

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

在给出ids = [1, 2, 3]的情况下会产生FIELD(id,?,?,?)的情况.不，这也不起作用.

Which would produce FIELD(id,?,?,?) given ids = [1, 2, 3]. Nope, this doesn't work either.

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

Experiment #3: Creating the entire, final SQL built from the ids, placing the raw ID values directly in the composed string. Besides being horrible, it doesn't work, either:

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

实验4::这使我进入了我提到的博客文章.在其中，作者使用了一组基于条件数量的预定义宏来解决or_where的缺失:

Experiment #4: This brings me around to that blog post I mentioned. In it, the author hacks around the lack of or_where using a set of pre-defined macros based on the number of conditions to pull together:

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带有纹理"，但它要求您具有有限且可管理的可用字段数.这可能是游戏改变者，也可能不是.

While this works and uses the ORM "with the grain" so to speak, it requires that you have a finite, manageable number of available fields. This may or may not be a game changer.

我的建议:不要试图解决ORM的漏洞.您知道最好的查询.如果ORM不接受它，则直接用原始SQL编写它，并记录ORM不起作用的原因.将其屏蔽在功能或模块后面，以便您保留将来更改其实现的权利.有一天，当ORM赶上来时，您就可以很好地重写它，而对系统的其余部分没有影响.

My recommendation: don't try to juggle around an ORM's leaks. You know the best query. If the ORM won't accept it, write it directly with raw SQL, and document why the ORM does not work. Shield it behind a function or module so you can reserve the future right to change its implementation. One day, when the ORM catches up, you can then just rewrite it nicely with no effects on the rest of the system.

这篇关于具有可变Arity的ecto查询和自定义MySQL函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！