Sorting in Grid viewer using JS API

It would be great to have in Grid viewer the ability to setup multiple user-defined sorting algorithms for

individual columns as well as without relation to any column. A generic form to implement sorting logic could be the standard in other languages “compare” function like

compare(nRowOne, nRowTwo, nColumn, grid),

where nRowOne, nRowTwo are indices of the compared Grid’s rows, nColumn is the column by which sorting is done (or -1 without relation to any column), grid is a reference to the Grid viewer. Very often in order to decide how to compare two rows, we need access to all values in the Data Frame. That’s why the last parameter is needed. Any solution will be appreciated.

We have recently added a number of ways grid sorting could be customized. Check out the following samples:

Using custom comparer
https://dev.datagrok.ai/js/samples/grid/order-rows-by-comparer

Specifying the sorted order manually
https://dev.datagrok.ai/js/samples/grid/order-rows

While it’s not exactly a row comparer that could be assigned to a column (currently this has to be done manually by overriding the default “sort” grid event), it’s a step in the right direction. I guess it would make sense to specify a column comparer like that:

let bmi = (i) => height.get(i) / weight.get(i);
grid.col("age").comparer = (i, j) => bmi(i) - bmi(j);

We are also thinking about the ability to automatically offer applicable comparers based on the column’s semantic type. Stay tuned, we’ll post updates here

P.S. This functionality is currently available only on the dev server.

I tested this new functionality and I stumbled upon a couple of issues:

  1. After setting the rows order from the second example the repaint operation of the Grid Viewer is extremely slow (it takes about 30 secs to repaint the grid when the array’s size is 1000 and several minutes when the size is 10000).
    view.grid.setRowOrder([big array]);

  2. The “comparer” field of any Grid’s column seems to be ignored after having been set and it arrives “undefined” before the assignment call. The sort call is triggered through Has this feature been already enabled or is still in the plans?

                      grid.col("age").comparer = (i, j) => bmi(i) - bmi(j);
    

This is all great and useful functionality, and it would be awesome to make it function.

After the rows order is set using the statement below, the Grid correctly repositions the cells for all the columns, but the row header cells’ order remains unchanged. While this exactly mimics the default behavior of Excel, in practice, instead of row numbers we use custom cell renderers for row header cells whose order has to be updated as well. Would it be possible to make an option to reposition the row header’s cells (perhaps as an additional Boolean argument to the call below)?

view.grid.setRowOrder([an array], bUpdateRowHeader);

Thanks for the reports! We will start looking at the performance degradation issue soon. The comparer field is not implemented yet, I was just thinking out loud and soliciting feedback :slight_smile:

Hi @dpetrov.gnf.org, can you please provide a script to reproduce the performance degradation issues when a custom ror order is provided? The script below works sets custom row order for 1 million rows, and everything still works instantaneously:

let t = grok.data.demo.demog(1000000);
let order = new Int32Array(t.rowCount);
for (let i = 0; i < t.rowCount; i++)
  order[i] = t.rowCount - i - 1;
  
let view = grok.shell.addTableView(t);
view.grid.setRowOrder(order);

Performance degradation happens when the rows get repositioned from a mouse click on the column header. In this case the Grid repaints all the cells, not just visible in the viewport. Please see the console log after clicking on the header. We use relatively heavy renderers where this degradation becomes quite noticeable.

let dframe = grok.data.demo.demog(1000);
let order = new Int32Array(dframe.rowCount);
for (let i = 0; i < dframe.rowCount; i++) {

    order[i] = dframe.rowCount - i - 1;
}

let bClicked = false;

let view = grok.shell.addTableView(dframe);
view.grid.onCellRender.subscribe(function (args) {

    if(args.cell.isTableCell) {
          if(!bClicked)
            return;

        let nCol = args.cell.gridColumn.idx;
        let nRow = args.cell.gridRow;
        console.log(nCol + " " + nRow);
    }
});


rxjs.fromEvent( view.grid.overlay, 'click').subscribe((e) => {
    let cell = view.grid.hitTest(e.offsetX, e.offsetY);

    let nButton = e.button;
    if (nButton === 0)
    {
        let cell = view.grid.hitTest(e.offsetX, e.offsetY);
        if (cell.isColHeader) {
            bClicked = true;
            view.grid.setRowOrder(order);
        }
    }
});
1 Like

Thanks for the example, it helped us easily find the problem; this is fixed now, the latest version will be available on the dev server in 2 hours.