Ecto query and custom MySQL function with variable arity

Question

I want to perform a query like the following one:

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

FIELD is a MySQL specific function, and 'B', 'A', 'D', 'E', 'C' are values coming from a List.

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

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

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

Answer 1

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.

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.

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

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

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

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, ","), ")"], "")))

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

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.

---

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.

Answer 2

Create a table with 2 columns:

B 1
A 2
D 3
E 4
C 5

Then JOIN LEFT(name, 1) to it and get the ordinal. Then sort by that.

(Sorry, I can't help with Elixir/Ecto/Arity.)

Answer 3

I would try to resolve this using the following SQL SELECT statement:

[Note: Don't have access right now to a system to check the correctness of the syntax, but I think it is OK]

SELECT A.MyID , A.MyName
  FROM (
         SELECT id                                   AS MyID            , 
                name                                 AS MyName          , 
                FIELD(name, 'B', 'A', 'D', 'E', 'C') AS Order_By_Field
           FROM mytable
       ) A
  ORDER BY A.Order_By_Field 
;

Please note that the list 'B','A',... can be passed as either an array or any other method and replace what is written in the above code sample.

Answer 4

This was actually driving me crazy until I found that (at least in MySQL), there is a FIND_IN_SET function. The syntax is a bit weird, but it doesn't take variable arguments, so you should be able to do this:

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