Multi-column sorting

优质
小牛编辑
133浏览
2023-12-01

Overview

The column sorting plugin 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. The sort operation is performed using a stable sort alghoritm regardless of the browser you use and the size of the data set which you sort.

Please keep in mind that you shouldn't enable both the columnSorting and multiColumnSorting plugins simultaneously.

Basic plugin configuration

The simplest way to enable the plugin is to just set the multiColumnSorting key to true. You will then be able to use the API methods and click on the header to sort, as you can see in the example below. Clicking on multiple column headers while holding the ctrl/cmd key will add the clicked columns to the sorting stack, and in the result, sort the them in the defined order. What's more, by default:

  • no column will be sorted initially,
  • a sorting indicator will be enabled,
  • empty cells won't be sorted,
  • the sort method will use default compare functions (read more about them here).

var arrayOfArrays = [
  ["Tesla", "Model 3", "BlueStar", "USA", "★★★★"],
  ["Tesla", "Model S", "WhiteStar", "USA", "★★★★★"],
  ["Mitsubishi", "iMiEV", "", "Japan", "★★"],
  ["Ford", "Focus EV", "", "USA", "★★"],
  ["Mitsubishi", "iMiEV Sport", "", "Japan", "★★"],
  ["Tesla", "Roadster", "DarkStar", "USA", "★★★★★"],
  ["Volkswagen", "e-Golf","", "Germany", "★★"],
  ["Volkswagen", "E-Up!", "", "Germany", "★★"],
  ["Ford", "C-Max Energi", "", "USA", "★"],
  ["BYD", "Denza", "", "China", "★★★"],
  ["BYD", "e5", "", "China", "★★★"],
  ["BYD", "e6", "", "China", "★★★★"]
];
      
var exampleContainer1 = document.getElementById('example1');

new Handsontable(exampleContainer1, {
  data: arrayOfArrays,
  colHeaders: ["Brand", "Model", "Code name", "Country of origin", "Rank"],
  multiColumnSorting: true
});

Extended plugin configuration

You can customize plugin options by providing configuration as an object. Here is the list of possible options to set:

  • initialConfig (Array), which determines the initial sort status for some columns; list of objects, which contain the following keys:
    • column (Number), which determines the visual index of the sorted column
    • sortOrder (String), which determines the order that the column will be sorted in (possible values: 'asc' and 'desc')
  • indicator (Boolean), which defines whether the sorting order indicator is displayed (an arrow icon in the column header specifying the sorting order),
  • sortEmptyCells (Boolean), which defines whether empty cells should take part in the sorting process,
  • headerAction (Boolean), which defines whether clicking the header should sort the table,
  • compareFunctionFactory (Function), which defines the compare function factory (described in more detail in this section).

Please take a look at the example plugin configuration below.

var arrayOfArrays = [
  ["Tesla", "Model 3", "BlueStar", "USA", "★★★★"],
  ["Tesla", "Model S", "WhiteStar", "USA", "★★★★★"],
  ["Mitsubishi", "iMiEV", "", "Japan", "★★"],
  ["Ford", "Focus EV", "", "USA", "★★"],
  ["Mitsubishi", "iMiEV Sport", "", "Japan", "★★"],
  ["Tesla", "Roadster", "DarkStar", "USA", "★★★★★"],
  ["Volkswagen", "e-Golf","", "Germany", "★★"],
  ["Volkswagen", "E-Up!", "", "Germany", "★★"],
  ["Ford", "C-Max Energi", "", "USA", "★"],
  ["BYD", "Denza", "", "China", "★★★"],
  ["BYD", "e5", "", "China", "★★★"],
  ["BYD", "e6", "", "China", "★★★★"]
];
      
var exampleContainer2 = document.getElementById('example2');

new Handsontable(exampleContainer2, {
  data: arrayOfArrays,
  colHeaders: ["Brand", "Model", "Code name", "Country of origin", "Rank"],
  multiColumnSorting: {
    sortEmptyCells: true,
    initialConfig: [{
      column: 0,
      sortOrder: 'asc'
    }, {
      column: 2,
      sortOrder: 'desc'
    }]
  }
});

Please keep in mind that options defined by the multiColumnSorting key in the main Handsontable settings apply to the entire table. Most of them can also be set for a particular column, as described in this section.

Default compare functions (sorting different kinds of data)

As in the native Array.sort method, our internal sorting alghoritm uses the compare function (also known as comparator). Different kinds of cells (like date, numeric, text) are treated in a different way. Each of them have their own comparator for sorting a particular data type.

As a result, you can see that different types of data are sorted properly. Handsontable simply needs the declared data type for the column, as you can see in the example below.

  var arrayOfObjects = [
    { brand: "Tesla", model: "Model 3", maxSpeed: 141, range: 215, seats: 5, price: 32750, productionDate: "06/29/2007" },
    { brand: "Tesla", model: "Model S", maxSpeed: 139, range: 275, seats: 7, price: 71788, productionDate: "04/02/2012" },
    { brand: "Mitsubishi", model: "iMiEV", maxSpeed: 81, range: 99, seats: 4, price: 31426.9, productionDate: "09/11/2009" },
    { brand: "Ford", model: "Focus EV", maxSpeed: 85, range: 100, seats: 4, price: 12000, productionDate: "10/01/2011" },
    { brand: "Mitsubishi", model: "iMiEV Sport", maxSpeed: 84, range: 124, seats: 4, price: 15000, productionDate: "05/11/2007" },
    { brand: "Tesla", model: "Roadster", maxSpeed: 125, range: 244, seats: 2, price: 113904.5, productionDate: "02/17/2008" },
    { brand: "Volkswagen", model: "e-Golf", maxSpeed: 87, range: 118, seats: 5, price: 33012, productionDate: "10/05/2011" },
    { brand: "Volkswagen", model: "E-Up!", maxSpeed: 85, range: 80, seats: 3, price: 32258.75, productionDate: "11/09/2009" },
    { brand: "Ford", model: "C-Max Energi", maxSpeed: 115, range: 18, seats: 5, price: 27120, productionDate: "11/25/2014" },
    { brand: "BYD", model: "Denza", maxSpeed: 93, range: 157, seats: 5, price: 47600, productionDate: "10/01/2011" },
    { brand: "BYD", model: "e5", maxSpeed: 93, range: 136, seats: 5, price: 22966.92, productionDate: "07/19/2015" },
    { brand: "BYD", model: "e6", maxSpeed: 87, range: 199, seats: 5, price: 31440, productionDate: "06/22/2009" }
  ];
  var exampleContainer3 = document.getElementById('example3');
  new Handsontable(exampleContainer3, {
    data: arrayOfObjects,
    colHeaders: ["Brand", "Model", "Max speed
(in miles per hour)", "Range
(in miles)", "Seats", "Price", "Start of
production"], columns: [{ data: 'brand' // 1st column is simple text, no special options here }, { data: 'model'// 2nd column is simple text, no special options here }, { data: 'maxSpeed', type: 'numeric' }, { data: 'range', type: 'numeric' }, { data: 'seats', type: 'numeric' }, { data: 'price', type: 'numeric', numericFormat: { pattern: '$ 0,0.00', culture: 'en-US' } }, { data: 'productionDate', type: 'date', dateFormat: 'MM/DD/YYYY', correctFormat: true, defaultDate: '01/01/1900' }], multiColumnSorting: true });

Custom compare functions

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. Something which we call a compare function factory must be placed under this key. The factory takes as parameters sortOrder and columnMeta and returns the compare function.

Please take a look at the example below which shows how the custom compare function factory should look.

function compareFunctionFactory(sortOrder, columnMeta) {
 return function comparator(value, nextValue) {
   // Some value comparisons which will return -1, 0 or 1...
 };
};

In the next section we’ll take a look how it may be used just for certain columns.

Plugin options for certain columns only

The plugin's options, such as compareFunctionFactory, sortEmptyCells, headerAction, indicator may be set just for a particular column. This can be done through the use of the columns option. The example below demonstrates how to disable the indicator and completely block sorting action for the first column.

    var arrayOfObjects = [
      { brand: "Tesla", model: "Model 3", maxSpeed: 141, range: 215, seats: 5, price: 32750, productionDate: "06/29/2007" },
      { brand: "Tesla", model: "Model S", maxSpeed: 139, range: 275, seats: 7, price: 71788, productionDate: "04/02/2012" },
      { brand: "Mitsubishi", model: "iMiEV", maxSpeed: 81, range: 99, seats: 4, price: 31426.9, productionDate: "09/11/2009" },
      { brand: "Ford", model: "Focus EV", maxSpeed: 85, range: 100, seats: 4, price: 12000, productionDate: "10/01/2011" },
      { brand: "Mitsubishi", model: "iMiEV Sport", maxSpeed: 84, range: 124, seats: 4, price: 15000, productionDate: "05/11/2007" },
      { brand: "Tesla", model: "Roadster", maxSpeed: 125, range: 244, seats: 2, price: 113904.5, productionDate: "02/17/2008" },
      { brand: "Volkswagen", model: "e-Golf", maxSpeed: 87, range: 118, seats: 5, price: 33012, productionDate: "10/05/2011" },
      { brand: "Volkswagen", model: "E-Up!", maxSpeed: 85, range: 80, seats: 3, price: 32258.75, productionDate: "11/09/2009" },
      { brand: "Ford", model: "C-Max Energi", maxSpeed: 115, range: 18, seats: 5, price: 27120, productionDate: "11/25/2014" },
      { brand: "BYD", model: "Denza", maxSpeed: 93, range: 157, seats: 5, price: 47600, productionDate: "10/01/2011" },
      { brand: "BYD", model: "e5", maxSpeed: 93, range: 136, seats: 5, price: 22966.92, productionDate: "07/19/2015" },
      { brand: "BYD", model: "e6", maxSpeed: 87, range: 199, seats: 5, price: 31440, productionDate: "06/22/2009" }
    ];
  
    var exampleContainer4 = document.getElementById('example4');
    new Handsontable(exampleContainer4, {
      data: arrayOfObjects,
      colHeaders: ["Brand
(non-sortable)", "Model", "Max speed
(in miles per hour)", "Range
(in miles)", "Seats", "Price", "Start of
production"], columns: [{ data: 'brand', // 1st column is simple text, no special options here multiColumnSorting: { indicator: false, headerAction: false, compareFunctionFactory: function compareFunctionFactory() { return function comparator() { return 0; // Don't sort the first visual column. }; } } }, { data: 'model'// 2nd column is simple text, no special options here }, { data: 'maxSpeed', type: 'numeric' }, { data: 'range', type: 'numeric' }, { data: 'seats', type: 'numeric' }, { data: 'price', type: 'numeric', numericFormat: { pattern: '$ 0,0.00', culture: 'en-US' } }, { data: 'productionDate', type: 'date', dateFormat: 'MM/DD/YYYY', correctFormat: true, defaultDate: '01/01/1900' }], multiColumnSorting: true });

Custom sort implementation

The plugin exposes the setSortConfig method, which was designed for the purpose of setting the internal sort state. Please keep in mind that the data set won't be sorted just by using this method. However, the isSorted and getSortConfig methods will return results corresponding to the previously set configuration. To use a custom sort implementation you have to:

  • provide a callback for the beforeColumnSort hook which will return false,
  • sort data by yourself (for example, on the server side),
  • set the internal state of the sort by using the setSortConfig method.

Please take a look at the custom sort implementation in the snippet below.

beforeColumnSort: function(currentSortConfig, destinationSortConfigs) {
  const columnSortPlugin = this.getPlugin('multiColumnSorting');

  columnSortPlugin.setSortConfig(destinationSortConfigs);

  // const newData = ... // Calculated data set, ie. from an AJAX call.

  // this.loadData(newData); // Load a new data set.

  return false; // The blockade for the default sort action.
}

Plugin hooks

The plugin provides two hooks:

  • beforeColumnSort, which as the name suggests is run before the sort. This means that the actual sort configuration obtained by the getSortConfig method within the callback will be equal to the sort configuration preserved before the hook call. The callback for beforeColumnSort, which will return false, will stop the table from being sorted. As a result, the afterColumnSort hook will not be called.
  • afterColumnSort, which is run always after the sorting, unless the callback for beforeColumnSort hook returns false.

Please keep in mind that hooks are also run when you use the clearSort method or provide a configuration that won't be processed (validation will fail).

Plugin API

List of methods exposed by the plugin: