DEV Community

Hugo Duksis for Elixir Berlin

Posted on

1

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)
Enter fullscreen mode Exit fullscreen mode

and in Ecto something like this:

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

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
Enter fullscreen mode Exit fullscreen mode

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]))
Enter fullscreen mode Exit fullscreen mode

and this will be the same as

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

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

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more →

Top comments (0)

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

Retry later