Ecto order by a dynamic fragment

Hugo Duksis — Tue, 20 Feb 2024 14:35:21 +0000

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

fragment function should be called with different arity based on values provided at runtime. (e.g. fragment/5, fragment/10, ...)
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

Reconsider upgrading Ecto
Try backporting splice
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

DEV Community: Hugo Duksis

Ecto order by a dynamic fragment

Problem description:

Solution splice:

Resources:

Solution `splice`: