MultiColumnSorting feature request

Tags: #<Tag:0x00007efc61b76da8> #<Tag:0x00007efc61b76c68>
#1

I have several columns that are formatted in HTML, or where the data presented isn’t directly sortable. For instance, I have a column called x_area that shows areas like this:

  900 m²
 40.4 ha
102.1 ha
  365 m²

Areas represented as m² look like 365 m<sup>2</sup>. Sorted into ascending order, they should look like this:

  365 m²
  900 m²
 40.4 ha
102.1 ha

Clearly, they can’t be sorted either textually or numerically. I can do it with a custom comparator, but parsing the areas is costly. However, I do have a column s_area_size that contains the raw area in m², and the sorted values look like this:

    365
    900
 404000
1021000

So I’m wondering if you’d consider introducing a sortOn attribute to the multiColumnSorting key. Then, when someone clicks the column header, the value from the sortOn attribute is passed to the comparator instead of the value from the cell.

The column item would then look like this:

{
  data: 'x_area',
  title: 'Area',
  multiColumnSorting: {
    sortOn: 's_area_size'
  },
  className: 'text-right',
  renderer: 'html'
}

Thanks for your consideration :slight_smile:

#2

Hi @phil.cairns

we’ve made an article about the sortFunction and here is an example similar to yours https://jsfiddle.net/handsoncode/htur2sau/?utm_source=website&utm_medium=embed&utm_campaign=htur2sau

This demo uses old settings (from when the post was written). If you are using the 6.0.0+ the sortFunction has been renamed and moved to the plugin settings. More about the changes can be read in our Release Notes.

If you’d have any issues with the implementation please let me know.

#3

Hi @aleksandra_budnik,

Thanks for your response. The article you mentioned has something similar to what I’m looking for: the example where the cities are sorted by area. However, it doesn’t seem to work at present. I loaded it into the fiddle, and clicking the column header just seems to sort things alphabetically. In fact, all of the examples on that page now appear to sort alphabetically for me: prices, weights, heights, distances and areas. I guess this is because the page is now using version 6.0.1, and the breaking change from the sortFunction is messing things up.

Regardless, I don’t think the areas example would work for me with the new MultiColumnSorting plugin. The function returned by the factory takes two values to be compared, and there is no way to find which rows are involved. The earlier version passed in an a pair of arrays with row number as the first item, and the value as the second item of each array. I think it would be very difficult to revert HOT back to passing in arrays instead of values since you’ve already published the breaking change, and I thought it might be easier to introduce a new sortOn attribute instead.

Unless I’m completely missing something obvious … ?

#4

Yes, sorry, we are preparing a new blog post - especially for the columnSorting changes as the compare function changed a lot.

Here is a demo that uses the latest comparator logic for kilometers and miles https://jsfiddle.net/handsoncode/gf19kcwb/

Let me know if it works for you as well.

ps. can we close MultiColumnSorting documentation suggestion?

#5

That fiddle is close to working :slight_smile:

You might want to change

if (newA < newB) {
  return abc ? -1 : 1;
}
if (newA > newB) {
  return abc ? 1 : -1;
}

into

if (newA < newB) {
  return abc === 'asc' ? -1 : 1;
}
if (newA > newB) {
  return abc === 'asc' ? 1 : -1;
}

As I said at the beginning, I can do it with a custom comparator. It does work, no problem. But parsing every value (possibly multiple times) before comparing is expensive, especially when there may be tens of thousands of items in the table. If there is no way to find values from other data attributes within the sort function, I’ll keep doing the parsing. I just think it would be much more efficient if I could compare using the value from another field, hence my suggestion.

If this is not a feature that you believe would fit the product, I’ll see if I can find my way through to do it, and let you know how it goes.

Yep, don’t mind if you close the documentation suggestion.

#6

I changed multiColumnSorting.js. In sortByPresetSortStates this:

const getDataForSortedColumns = visualRowIndex =>
  arrayMap(sortedColumnsList, physicalColumn => this.hot.getDataAtCell(visualRowIndex, this.hot.toVisualColumn(physicalColumn)));

became:

const getDataForSortedColumns = visualRowIndex =>
  arrayMap(sortedColumnsList, physicalColumn => 
    this.columnMetaCache.get(physicalColumn).hasOwnProperty('sortOn')
      ? this.hot.getDataAtRowProp(visualRowIndex, this.columnMetaCache.get(physicalColumn).sortOn)
      : this.hot.getDataAtCell(visualRowIndex, this.hot.toVisualColumn(physicalColumn)));

I don’t know if it’s going to be generally applicable, or if it works with your coding style, but it works for me. The syntax is as per the initial post.

I don’t mind if you close this.

#7

Hey Phil,

Can I ask you to create a pull request? I will ask our developer who made the refactor to take a look at it.

#8

Hi Aleksandra,

You can certainly ask, but as much as I’d like to, I had a look at the contribution rules and it’s beyond what I can provide at present. I use mercurial instead of git, and after a few minutes looking around, it wasn’t clear how to make a branch and a PR in your environment … pretty much all I’ve done with git is clone things. I’m happy for your developer to use the code as it stands (I hereby put it under the MIT licence), but be aware there are no test cases, and nothing in there that checks the validity of the field specified in the sortOn attribute.

Cheers,
Phil.

#9

Thank you for taking the time to check it out.

I will ask a developer to examine the code. We always try to add some tests. The library is too heavy and complicated to rely on hunch.

#10

I just got a reply

This is interesting proposal, but we don’t change API right now, because don’t want to make breaking change again in columnSorting and multiColumnSorting.

#11

No worries. I can’t see how it’s a breaking change, but I don’t have a problem with the devs not wanting to incorporate it … it’s working for me now, and if they want to include it later, that’s entirely up to them.

All the best, and thanks for your help and the entire library. It’s making my product a lot easier to use :slight_smile:

#12

Turns out I was completely missing something obvious. I could have had the numeric value in square metres for that column and simply used a custom formatter to render the area. Areas greater than 10,000 are in hectares, while others are in m². D’oh :unamused:

1 Like