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

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

The jobs table records will be returned in the order which we have specified in the @statuses_order module attribute string.

Read more here here,