Reopened : colOps on paginated data (was getFilteredData on pagination) #332

Open
cjohnsonuk opened this Issue Nov 2, 2016 · 6 comments

Projects

In QA in Refactoring/tech debt

2 participants

@cjohnsonuk

Looking back at this again...
We're reviewing another piece of code that uses colOps to display a sum in a named div as per the demo.

On your demo page : http://koalyptus.github.io/TableFilter/column-calculations.html

run this in the console : (it's basically the same as is on the page already but with pagination turned on)

tf.destroy();
var tf = new TableFilter(
        document.querySelector('#calc-demo'),
        {
            base_path: 'tablefilter/',
            alternate_rows: true,
            rows_counter: true,
            rows_counter_text: 'Items: ',
            btn_reset: true,
          paging: true,
          results_per_page: ['Results per page',[5,10,25,50,100]],  
          status_bar: true,
            col_types: [
                'none', 'string', 'number',
                'number', 'number', 'number',
                'number', 'number', 'number',
                'number', 'number', 'number',
                'number', 'number', 'number',
                'number'
            ],
            sort_select: true,
            col_1: 'select',

            // extensions: sort and column operations
            extensions: [
                {
                    name: 'sort'
                }, {
                    name: 'colOps',
                    id: [
                        'demosum2', 'demomean2', 'demomin2', 'demoLQ2', 'demoMed2', 'demoUQ2', 'demomax2',
                        'demosum3', 'demomean3', 'demomin3', 'demoLQ3', 'demoMed3', 'demoUQ3', 'demomax3',
                        'demosum4', 'demomean4', 'demomin4', 'demoLQ4', 'demoMed4', 'demoUQ4', 'demomax4',
                        'demosum5', 'demomean5', 'demomin5', 'demoLQ5', 'demoMed5', 'demoUQ5', 'demomax5',
                        'demosum6', 'demomean6', 'demomin6', 'demoLQ6', 'demoMed6', 'demoUQ6', 'demomax6',
                        'demosum7', 'demomean7', 'demomin7', 'demoLQ7', 'demoMed7', 'demoUQ7', 'demomax7',
                        'demosum8', 'demomean8', 'demomin8', 'demoLQ8', 'demoMed8', 'demoUQ8', 'demomax8',
                        'demosum9', 'demomean9', 'demomin9', 'demoLQ9', 'demoMed9', 'demoUQ9', 'demomax9',
                        'demosum10', 'demomean10', 'demomin10', 'demoLQ10', 'demoMed10', 'demoUQ10', 'demomax10',
                        'demosum11', 'demomean11', 'demomin11', 'demoLQ11', 'demoMed11', 'demoUQ11', 'demomax11',
                        'demosum12', 'demomean12', 'demomin12', 'demoLQ12', 'demoMed12', 'demoUQ12', 'demomax12',
                        'demosum13', 'demomean13', 'demomin13', 'demoLQ13', 'demoMed13', 'demoUQ13', 'demomax13',
                        'demosum14', 'demomean14', 'demomin14', 'demoLQ14', 'demoMed14', 'demoUQ14', 'demomax14',
                        'demosum15', 'demomean15', 'demomin15', 'demoLQ15', 'demoMed15', 'demoUQ15', 'demomax15'
                    ],
                    col: [
                        2, 2, 2, 2, 2, 2, 2,
                        3, 3, 3, 3, 3, 3, 3,
                        4, 4, 4, 4, 4, 4, 4,
                        5, 5, 5, 5, 5, 5, 5,
                        6, 6, 6, 6, 6, 6, 6,
                        7, 7, 7, 7, 7, 7, 7,
                        8, 8, 8, 8, 8, 8, 8,
                        9, 9, 9, 9, 9, 9, 9,
                        10, 10, 10, 10, 10, 10, 10,
                        11, 11, 11, 11, 11, 11, 11,
                        12, 12, 12, 12, 12, 12, 12,
                        13, 13, 13, 13, 13, 13, 13,
                        14, 14, 14, 14, 14, 14, 14,
                        15, 15, 15, 15, 15, 15, 15
                    ],
                    operation: [
                        'sum', 'mean', 'min', 'Q1', 'median', 'Q3', 'max',
                        'sum', 'mean', 'min', 'Q1', 'median', 'Q3', 'max',
                        'sum', 'mean', 'min', 'Q1', 'median', 'Q3', 'max',
                        'sum', 'mean', 'min', 'Q1', 'median', 'Q3', 'max',
                        'sum', 'mean', 'min', 'Q1', 'median', 'Q3', 'max',
                        'sum', 'mean', 'min', 'Q1', 'median', 'Q3', 'max',
                        'sum', 'mean', 'min', 'Q1', 'median', 'Q3', 'max',
                        'sum', 'mean', 'min', 'Q1', 'median', 'Q3', 'max',
                        'sum', 'mean', 'min', 'Q1', 'median', 'Q3', 'max',
                        'sum', 'mean', 'min', 'Q1', 'median', 'Q3', 'max',
                        'sum', 'mean', 'min', 'Q1', 'median', 'Q3', 'max',
                        'sum', 'mean', 'min', 'Q1', 'median', 'Q3', 'max',
                        'sum', 'mean', 'min', 'Q1', 'median', 'Q3', 'max',
                        'sum', 'mean', 'min', 'Q1', 'median', 'Q3', 'max'
                    ],
                    decimal_precision: [
                        1, 1, 1, 1, 1, 1, 1,
                        1, 1, 1, 1, 1, 1, 1,
                        1, 1, 1, 1, 1, 1, 1,
                        1, 1, 1, 1, 1, 1, 1,
                        1, 1, 1, 1, 1, 1, 1,
                        1, 1, 1, 1, 1, 1, 1,
                        1, 1, 1, 1, 1, 1, 1,
                        1, 1, 1, 1, 1, 1, 1,
                        1, 1, 1, 1, 1, 1, 1,
                        1, 1, 1, 1, 1, 1, 1,
                        1, 1, 1, 1, 1, 1, 1,
                        1, 1, 1, 1, 1, 1, 1,
                        1, 1, 1, 1, 1, 1, 1,
                        1, 1, 1, 1, 1, 1, 1
                    ]
                }
            ]
        }
    );
    tf.init();

and you will see that the colOps operations don't like data that's paginated only calculating the sum of the visible rows on the first page.
EG in column A filter box type <40 and it shows 5 rows out of a total of 15 but the col ops is working on visible / displayed data rather than all the filtered rows which (certainly in my case) would be a lot more useful. Assuming that its currently functioning correctly and showing "Displayed rows" then you would expect changing the page number to view the 2nd and 3rd pages would also recalculate the sum of displayed rows but it doesn't. It still shows the sum of the 5 values on page 1.
I'm only using pagination to minimise the amount of scrolling to see the stats in the colOps table.

We'd been using ColOps to calculate a sum, but as the data set got bigger and the table paginated the ColOps sum stopped using the whole table....

@koalyptus
Owner

After a quick investigation, it looks like ColOps extension is simply not subscribed to the paging events. That's why is not reacting to page changes... Hopefully just a question of subscribing to the change page events.

@koalyptus koalyptus pushed a commit that referenced this issue Nov 2, 2016
Max Guglielmi Addressed #332 and continued #319 b994059
@koalyptus
Owner

With v0.4.6 should now behave as expected.

@cjohnsonuk

Thanks. That's great and looks a lot more consistent with the label in the demo.

Is it possible to add a Boolean option to colOps to get it to operate on visible (true) or filtered (false) data so that we can show stats for filtered data that just happens to be paged?

That way if/when the option to toggle pagination on or off is used the same function can be used to do the calculations. I'm sure you did something similar to this with the "get filtered data" to omit columns that were hidden. We're not currently using colOps in this project for this reason.

@koalyptus
Owner

If my understanding is correct, when paging is on you'd like to have the possibility to show stats for the current page (expected behaviour BTW) and also for the whole filtered paged data. If this is the case, because of its implementation ColOps currently does not allow that.
Instead of providing a configuration option, I would refactor the module to expose an API with a separation of concerns as currently its interface exposes a single method called calc which does everything, calculations and displaying the results as per configuration.
So having a simple API focusing only on making the desired calculation on a specified column and one for displaying the results would be flexible enough for any custom requirement.

@koalyptus
Owner
koalyptus commented Jan 15, 2017 edited

Starting from v0.4.34, ColOps exposes an API that should allow you to perform desired calculations on visible/filtered data.
For the API refer to http://koalyptus.github.io/TableFilter/docs/class/src/extensions/colOps/colOps.js~ColOps.html
For the configuration options and custom events: https://github.com/koalyptus/TableFilter/wiki/3.2-Column-operations

Below an example of how to hook into the before-column-calc event to calculate the sum of the filtered data of a column when paging is on:

tf.emitter.on(['before-column-calc'], function(tf, colOps, colIndex, values, operation, precision) { 
  if(operation === 'sum') {
        /** getFilteredDataCol accepts in order of appearance:
              - a column index
              - an optional boolean indicating whether the returned dataset also inclues the colunm header
              - an optional boolean indicating whether the returned dataset should return numbers (string by default)
              - an optional array of row indexes to be excluded from the returned dataset
              - an optional boolean indicating whether the returned dataset should only include filtered and visible data (true by default) or filtered only (paging can contain filtered data which is not visible)
              Refer to: http://koalyptus.github.io/TableFilter/docs/class/src/tablefilter.js~TableFilter.html#instance-method-getFilteredDataCol
        */ 
	var colValues = tf.getFilteredDataCol(colIndex, false, true, [], false);
	var result = Number(colOps.calcSum(colValues));
        document.getElementById('my-label').innerHTML = result.toFixed(precision);
  }
});

Tell me how it goes.

@koalyptus
Owner

Added the Column calculations with API demo to show how to use ColOps API.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment