Ecto order by a dynamic fragment

Problem description:

Urgent need to return the results of a query in a predefined sort order by id. The project uses Ecto, MySQL and in SQL the sorting would look like this:

ORDER BY FIELD(id, 5, 1, 2)

and in Ecto something like this:

|> order_by(q, fragment("FIELD(?, ?, ?, ?)", q.id, 5, 1, 2))

Looks good! dosen't it?

No. The problem is that the list of ID's is dynamic, including its size. And this means two things

1. fragment function should be called with different arity based on values provided at runtime. (e.g. fragment/5, fragment/10, ...)
2. first parameter of the fragment function changes at runtime as well because we need as many question marks as there are elements in the list + 1 for the field reference.

And out of nr. 2 comes another problem.
We can not just pass an interpolated string to fragment as this is not allowed do to potential SQL injections.

(Ecto.Query.CompileError) to prevent SQL injection attacks, fragment(...) does not allow strings to be interpolated as the first argument via the `^` operator

Solution splice:

If you are or have the possibility to upgrade your Ecto version to 3.11 or above you can use splice(list)

fragment("? in (?)", p.id, splice(^[1, 2, 3]))

and this will be the same as

from p in Post, where: fragment("? in (?,?,?)", p.id, ^1, ^2, ^3)

I first encountered this problem before ecto 3.11 and if you are on an older project and not able to upgrade your version of ecto there are few options for you

1. Reconsider upgrading Ecto
2. Try backporting splice
3. Write a less sophisticated macro that serves your specific needs

Resources:

https://hexdocs.pm/ecto/3.11.0/Ecto.Query.API.html#splice/1
https://stackoverflow.com/questions/59042043/using-unquote-splicing-in-macros-with-modified-lists