Ecto order_by text field

This post demonstrates how to order an ecto query by a text field.
by sending the order raw sql query through Ecto.Query.API.fragment/1

Sample table

Assuming we want to query a table containing a list of scheduled jobs with known statuses. e.g
RUNNING, PENDING, COMPLETED, FAILED

id	task_ref	status	last_exec_timestamp
1	2232323	PENDING	1605726253
2	2232324	FAILED	1605726153
4	2132326	RUNNING	1605726233
5	2032326	RUNNING	1605726233
6	2932326	FAILED	1605726233
7	2233326	COMPLETED	1605726233
8	2238326	FAILED	1605726233

Our objective is to:

• Fetch all rows
• Order the rows in the following order RUNNING > PENDING > COMPLETED > FAILED

Off we go

 #SomeModule
 import Ecto.Query

 @statuses_order """
    (case(?)
      when 'RUNNING' then 1
      when 'PENDING' then 2
      when 'COMPLETED' then 3
      else 4
    end)
  """
 def your_function do
    from(j in Jobs)
    |> order_by([j], asc: fragment(@statuses_order, j.status))
    |> Repo.all()
 end

Just like that our jobs will be returned in the order which we have specified in the @statuses_order module attribute.

More information about this subject from elixir forum can be found here.

Thats it! :)