Ecto - how to get records by ids in exact order

Question

I have a list of record ids - [9, 1, 4, 3].

I want to retrieve records from postgresql and want them be ordered as in this ids list. But when I make a query, records are returned in arbitrary order:

Ecto.Query.from(r in Record, where: r.id in [9, 1, 4, 3]) 
  |> Repo.all()
  |> Enum.map(&Map.get(&1, :id)) # => [4, 9, 1, 3]

How could I retrieve records with the same order?

Answer 1

You can use the PostgreSQL's array_position function and Ecto's fragment function. In your case, it would look like:

Ecto.Query.from(r in Record, where: r.id in [9, 1, 4, 3])
|> Ecto.Query.order_by([r], fragment("array_position(?, ?)", [9, 1, 4, 3], r.id) 
|> Repo.all()

I would avoid processing data outside of the database engine. It shouldn't matter in this simple example. However, it might impact performance for bigger data sets or more complex data structures because first, you have to load the results into the memory and then perform operations on them to change the order.