Question 89799 in PostgreSQL Question posted in
The official documentation can be found here.

Rails conditional order by priority and status – Postgresql

I want to order my Order model by priority, so that the ones with priority: true and with status != 'delivered' are on top. The ones with priority: true and status: delivered should be in the table below with all other orders sorted by updated_at.

In the end it should be sorted like this:

ID     PRIORITY     STATUS          UPDATED_AT
5      true         ready           1 min ago
8      true         in_transport    5 min ago

13     false        ready           less than a min ago
1      true         delivered       3 min ago         
2      false        delivered       15 min ago
4      true         delivered       16 min ago
10     false        in_transport    20 min ago

I managed to achieve this partially with:

orders = Order.where(...)
sql = "(CASE WHEN (priority = true AND status = 'delivered') THEN 1 ELSE 0 END), priority desc"

orders.order(sql)

With this query the priority ones which are delivered are pushed to the end of the list and which I don’t want:

ID     PRIORITY     STATUS          UPDATED_AT
5      true         ready           1 min ago
8      true         in_transport    5 min ago

13     false        ready           less than a min ago
2      false        delivered       15 min ago
10     false        in_transport    20 min ago 
...
1      true         delivered       3 min ago         
4      true         delivered       16 min ago

I would appriciate any guidelines here.

Tags:

2

Answers


  1. 0

    A possible approach is to use two separate queries and combine them.

    Something like (not tested):

    orders = Order.where(...)

priority_orders = orders.where(priority: true).where.not(status: 'delivered')
other_orders = orders.where.not(id: priority_orders.ids)

result = 
  priority_orders.order(updated_at: :desc) + 
  other_orders.order(priority: :desc, status: :asc, updated_at: :desc)
    Login or Signup to reply.
  2. 0

    One approach would be using multiple order clause.

    sql = "(CASE WHEN (priority = true AND status = 'delivered') THEN 1 ELSE 0 END), priority desc"

    update the above to:

    orders.order("CASE WHEN (priority = true AND status != 'delivered') THEN 0 ELSE 1 END, updated_at desc")

    The query is self explanatory but let me know if you need any detail.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search