DEV Community

Kolawole O. Gabriel
Kolawole O. Gabriel

Posted on

8 1

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

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

Image of Datadog

Measure and Advance Your DevSecOps Maturity

In this white paper, we lay out a DevSecOps maturity model based on our experience helping thousands of organizations advance their DevSecOps practices. Learn the key competencies and practices across four distinct levels of maturity.

Get The White Paper

Top comments (0)

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up