How to sort table in ecto based on two columns

user_7153915 0
Viewed 500

I have table with time and operation column. Operation column includes create, update and delete values and time has elixir utc datetime. I want to sort based on datetime and if multiple rows are present based on same time stamp I want to first take delete rows and than create and than update.

Wrong:

1. { time: TIME1, operation: "create" }
2. { time: TIME1, operation: "delete" }
3. { time: TIME1, operation: "update" }

Correct

1. { time: TIME1, operation: "delete" }
2. { time: TIME1, operation: "create" }
2. { time: TIME1, operation: "update" }

So it would always give priority to delete and than create would be selected and values related to update should come at last.

If we do it ecto using following query where it would always accept "delete" as first value but it would not sort update and create rows. I am curious to know if we could somehow extend usage of fragments in this query to have sorting based on all create, update and delete fields.

     |> order_by([e],
       asc: e.time,
       desc: e.operation == "delete"
      )
ecto elixir
1 Answers

You can try this (if you use postgres):

|> order_by([e],
  asc: e.time,
  asc: fragment("CASE ? WHEN 'delete' THEN 1 WHEN 'create' THEN 2 WHEN 'update' THEN 3 END", e.operation)
)

Details about such ordering here: sql ORDER BY multiple values in specific order?

user_25280060
Related