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()

Stay up to date

Sign up for the mailing list and get notified via email when new blog posts come out.