Ecto With Ordinality
Ecto with ordinality
We want to query the post where the id is 1, 2, 4, 3 and keep this order The best solution to do that using postgres is WITH ORDINALITY
SELECT p.*
FROM posts p
JOIN unnest('{1,2,4,3}'::int[]) WITH ORDINALITY t(id, ord) USING (id)
ORDER BY t.order;
Complete query
def list_post_where_id_in_order(ids) do
query =
from post in Post,
inner_join:
ordinality in fragment(
"SELECT * FROM UNNEST(?::int[]) WITH ORDINALITY as ordinality (id, num)",
^ids
),
on: post.id == ordinality.id,
order_by: [asc: ordinality.num]
Repo.all(query)
end
iex> list_post_where_id_in_order([1, 2, 4, 3])
Composable query
def post_by_id_in_order(query, ids) do
query
|> join(
:inner,
[post],
ordinality in fragment(
"SELECT * FROM UNNEST(?::int[]) WITH ORDINALITY as ordinality (id, num)",
^ids
),
on: post.id == ordinality.id,
as: :ordinality
)
|> order_by([post, ordinality: ordinality], asc: ordinality.num)
end
iex> Post |> post_by_id_in_order([1, 2, 4, 3]) |> Repo.all()
Read other tils