Skip to main content

One post tagged with "SQL"

View All Tags

· 3 min read
Yahya Gilany

In this post, I'll demonstrate 2 ways to perform custom sorting. It all happened when a client asked to sort a lookup table/dropdown in a specific order. S -> U -> N

Example:​

Statuses:

|id |     name      |
|---|:-------------:|
| 1 | Complete (S)  |
| 2 |Dropped out (U)|
| 3 |in progress (N)|
| 4 |Lost Funds (U) |

Usually what I'd do is adding a sort_order column to the table in the database and use it when querying the records. As such,

|id |     name      | sort_order |
|---|:-------------:|:---------- |
| 1 | Complete (S)  |      1     |
| 2 |Dropped out (U)|      2     |
| 3 |in progress (N)|      4     |
| 4 |Lost Funds (U) |      3     |

and the query would simply be:

    SELECT *
    FROM statuses
    ORDER_BY sort_order

This is obviosly the cleanest and the recommended way to approach this problem.

However, in this particular case, we weren't at liberty to make changes to the database design. So, we had to discuss performing the sort at the query time with some other custom methods, OR processing it using JS (The application in question is Node.JS/Angular Application).

Solution1: Custom Order using SQL​

The first Solution involved Regular expressions to extract the last bits of the names (S), (N), (U) Which are what will be used in the sorting

SELECT *
FROM statuses
ORDER BY 
    CASE substring(name from '\([A-Z]\)')
        WHEN '(S)' THEN 1
        WHEN '(U)' THEN 2
        WHEN '(N)' THEN 3
        ELSE 5
    END

Custom Order by processing the data in JS​

Given that we already had a specific criteria for the sort (S -> U -> N), I created a variable sorter of those 3 values along with the corresponding desired order. Javascript sort function allows for custom comparing functions to be used in the sorting process. So, we will be creating this comparing function compare(). What this function basically does, is that it uses the same regular expression to extract the bit with the parantheses, For each element it finds the correspondoing order from the sorter variable and uses that as a comparer.

var compare = function(a, b) {
  var sorter = [{value: "(S)", order: 1}, {value: "(U)", order: 2}, {value: "(N)", order: 3}]

  var a_status = a.name.match(/\([A-Z]\)/g)
  var b_status = b.name.match(/\([A-Z]\)/g)
  
  return sorter.find(function(element) { return element.value == a_status[0]; }).order - sorter.find(function(element) { return element.value == b_status[0]; }).order
}

var sorted = release.sort(compare)
console.log(sorted)

Eventually we opted to the second solution. We use an ORM Node Module and making the custom order in sql wouldn't have been as easy to implement.

Confession:​

I totally forgot about adding the sort_order Column until the time I wrote this blog. But again we weren't have been able to use it anyway. Cudos to Rubber Ducking!!!