Feature spotlight: Multi-column sorting

Wojciech Szymański Features / November 5, 2018

Feature spotlight: Multi-column sorting

For up-to-date Handsontable documentation, see handsontable.com/docs.

In Handsontable 6.0.0, we gave our data grid a new functionality. The ability to sort multiple columns was the most voted for change by far, as our roadmap shows, so we set a course towards adding this new functionality. Travelling this path then instigated some large-scale modifications to the library, like reorganizing the way sorting works as a whole.

In this post, we will describe not only the noteworthy changes that were made, but also the technical aspects of sorting, as well as custom mods that can be useful for adapting Handsontable to your own needs.

As I mentioned above, version 6 comes with a new functionality. We also improved the columnSorting plugin to allow for greater efficiency and smooth integration with the multiColumnSorting plugin (which, when enabled, will allow you to add more columns by clicking on the their headers with the ctrl/cmd key pressed). Moreover, we added the option to disable the action of sorting a column when you click on its header.

We changed the appearance of the sort indicators as well, and as soon as either plugin is enabled, the appropriate indicator will become visible in the header(s) of the column(s) being sorted. The multiColumnSorting plugin will also display the order in which the columns are sorted. (Currently, we are working on the introduction of several other styles of sorting icons. Follow us on Twitter so that you don’t miss being notified when we release them.)

Detailed information about the Column sorting and Multi-column sorting plugins can be found in our documentation. Now, let’s take a look at how the data set is organized.

Sorting mechanism

So how does sorting work? Well, let’s see what the documentation says about it:

Column sorting works as a proxy between the datasource and the Handsontable rendering module. It can map indices of displayed rows (called “visual indices”) to the indices of corresponding rows in the datasource (called “physical indices”) and vice versa. This way you can alter the order of rows which are being presented to a user, without changing the datasource’s internal structure.

What’s more…

The sort operation is performed using a stable sort algorithm regardless of the browser you use and the size of the data set which you sort.

What does this say, and why is it so important?

Stability in sorting algorithms

In Handsontable, we can declare data as an array of arrays or an array of objects. In both cases, data sorting should not replace the order of elements if they have the same value in the sorted column. Since the data in the other columns of the element typically are significant, the original order of elements with the same value should be mapped. Let’s look at the example below to see the difference between stable and unstable sorting.

Example of a stable sort

Stable sorting maintains the order of indexes within groups of elements with the same value in the sorted column (as a consequence, the color gradient from the lightest to the darkest has also been preserved).

Example of an unstable sort

In unstable sorting, the elements within the group may be reorganized.

The predictability of how Handsontable works is very important to us. A while back, in version 0.31.0, an important change was introduced to ensure that the sorting of columns was done using a stable sorting algorithm.

The way that the current ECMA-262 standard (ECMAScript 2018) specifies the sorting of tables is the same as at the time of making changes to version 0.31.0:

The elements of this array are sorted. The sort is not necessarily stable (that is, elements that compare equal do not necessarily remain in their original order).

Back then, due to the fact that the language specification did not force the Array.sort function to use a stable sorting algorithm, we needed to look at the operation of the browsers we supported. What we found was that Google Chrome, as well as Microsoft Edge and Internet Explorer 9+, implemented Array.sort using algorithms that were not stable, so we were forced to abandon the use of the function for our needs.

It is worth mentioning that a stable version of the V8 engine has recently been released and it changes the way the Array.sort function works, solving one of the oldest issues on their project board. Thus, Google Chrome (from version 70) uses a stable algorithm for sorting tables. However, this does not change the fact that we still have to use an alternative solution for array sorting, because we do support Microsoft browsers as well.

In Handsontable, we decided to implement an algorithm that would allow for predictable table behavior. As such an algorithm, we chose merge sort and decided not to reinvent the wheel – we used the code published under the MIT license from the javascript-algorithms repository. At the cost of a more efficient solution in the form of a native function, we provide the desired table behavior, regardless of whether you use the internal compare function or a custom one.

Custom compare functions

Starting with Handsontable 6.0.0, we have the ability to pass the compare function factory to the settings of a specific column. Here’s what the documentation says:

You can provide your own compare function to the sorting algorithm. This function should look the same as an argument in the native Array.sort method (read the description here). It is handled by the plugin when compareFunctionFactory is defined in the configuration.

In it, there is an example in which we completely disabled the sorting for a specific column. Below, we will demonstrate a different use case.

Imagine we want to create a ranking so that the Rank column always has a fixed number of stars, filled or not.

A sample table that rates different car models out of 5 stars

After clicking on the column containing the rankings, the data is sorted using the standard sorting function. This, like in Array.sort, will compare our values lexically.

What lexicographic sorting does is compare the representation of characters (strings). For example, “80” will be before “9” in lexicographical order, although, numerically, 9 precedes 80. In our case, since the “★” character has a lower Unicode code point value than the “☆” character, standard sorting would work completely the opposite of how we would expect (the filled star character would be placed before the empty star character when performing an ascending sort). However, in a very simple way, we can create a function that will allow us to sort the column with the rankings properly, focusing only on filled stars. Let’s take a look at how it works.

Custom sort implementation

In the documentation, we mention that it is possible to set a custom sort implementation. In the following example I will show you how to achieve this goal. We will collect data from the beginning to the end using AJAX queries. Please bear in mind that there may be a slight lag. We’ve created a very simple example of an optimistic UI – there is an extra render call after invoking the plugin’s setSortConfig function.

Over to you

Hopefully you’ve found this post informative and helpful. Has the multi-column sorting feature proven useful to you? What type of app are you using it in? Let us know about your experience in the comments section below.