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

Anissaanita

i am not understand. But if you want to oder the Result only by the first letter of the field name use : ORDER BY FIELD(LEFT(name,1), 'B', 'A', 'D', 'E', 'C') to compare it with the List – Touchandgo

@BerndBuffen The question is about Ecto way of writing this query, not whether the query works for me or not. – Cheffetz

- sorry for my comment – Touchandgo

@MladenJablanović - not familiar with Ecto but out of curiosity I've come across this while searching, it has 4 sections in the table contents that relate to using fragment, maybe this will give you some ideas if you haven't read the contents already: elixirnation.io/references/ecto-query-examples – Watchdog

Yeah, read that page already (a fine "cookbook" collection), but it does not help in this case, because of dynamic number of arguments in my case, and fragment is, being a macro, processed in compile-time. Thanks for linking it, though, it may be helpful for others. – Cheffetz

Alas, another case of 3rd party software getting in the way? – Cloudland

@RickJames pros and cons of using ORMs in general is already a controversial topic, I wouldn't like to digress into that one here, but yes, I guess you could put it that way. – Cheffetz

Concatenate the columns in Elixir and pass as a predetermined string to fragment? See also this ecto issue. – Sceptic

@Sceptic that sounds like one possible solution, though not much more elegant than raw SQL. Could you try writing an answer with an example? How would I go about sanitizing the arguments? – Cheffetz

Can you show what you've tried with fragment so far? – Sceptic

@Sceptic see the edit above. – Cheffetz

There is a (quite) simple solution with pure SQL, but I guess you are not interested, are you? – Vagary

@Vagary not sure what you have in mind, I am interested in whatever solves my problem with least headache. – Cheffetz

OK. I'll put it as an answer if few minutes. – Vagary

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.

Sceptic

Thanks for the elaborate writeup! This roughly sums up my findings too. In our case, even this orderby_fragment overloading wouldn't fly, as the list can have up to hundreds of items, in our case. I am not even sure FIELD() is viable approach at all, but that's a different issue. – Cheffetz

Also, Ecto issue #1485 that you linked is exactly the same case that I have here. I will try to reopen the issue, and perhaps give a shot at creating the patch. – Cheffetz

@MladenJablanović Definitely would ask @ JoseValim to consider supporting this in Ecto as, nearly as I can tell, it's outside the scope of what Ecto can do currently. – Sceptic

Ecto does not support dynamic fragments since it breaks a very important feature of Ecto - when you use ecto query API there's no way to introduce SQL injection. As soon as you allow dynamic fragments you break that guarantee. It's one of the most important features of Ecto in my opinion. Your proposed solution with Enum.join does not work as ecto enforces fragments to be compile-time string constants. – Whoop

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

Cloudland

1) B, A, D, E, C is the input coming from the user with every API request, so I can't possibly have it prepared in advance and 2) having a special table just to work around the ORM seem absurd – Cheffetz

Do the sorting in the ORM? – Cloudland

ORM does not do the sorting, I can do it in Elixir, but it complicates the code somewhat, and is probably inferior comparing to sorting by MySQL performance-wise. But that's certainly a fallback solution I am considering. – Cheffetz

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.

Vagary

As far as I can see, this only moves FIELD call to SELECT clause. How would I write it in Ecto? – Cheffetz

I have no idea. The point is that you send to the DB a standard SELECT that returns you the result in the desired order, so no need to any sorting at all. Since the result is already sorted, you should pass it just like you pass any other SQL command (I'm guessing because I know nothing about Ecto). – Vagary

The point of Ecto (and any ORM in general) is NOT passing SQL. :) This question is all about ways to write the Ecto code which produces this particular SQL, not SQL itself. – Cheffetz

Well, if you recall my initial comment was There is a (quite) simple solution with pure SQL, but I guess you are not interested, are you? and your answer @Vagary not sure what you have in mind, I am interested in whatever solves my problem with least headache. Though not the specific answer you are looking for (and I'm sorry it isn't), it complies with my and your comments I think. Since I have no knowledge about Ecto at all, I can't help you I'm afraid. – Vagary

As Ecto (and most, if not all other ORMs) support some form of passing (almost) pure SQL to the database, I thought you were referring to that. Sorry for misunderstanding. Also, the solution in pure SQL I already have (it stands right there in the question). – Cheffetz

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

Wendling

You should mention this also in the comment of the Ecto issue #1485, really helpful! – Xanthene

It seems like I did (?? 😅) github.com/elixir-ecto/ecto/issues/1485#issuecomment-638779586 – Wendling

Good point, I just linked to here from there 😁 – Wendling