DEV Community

loading...

Ecto order_by text field

segaz2002 profile image Kolawole O. Gabriel ・1 min read

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

Enter fullscreen mode Exit fullscreen mode

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! :)

Discussion (0)

Forem Open with the Forem app