How to filter the JSON rows to populate the DataTable?

nya13

So I'm creating a DataTable using a JSON. Without having the pre-manipulate the original JSON array before calling DataTable(), does DataTables provide a way when to load the data and ignore rows 0, 5, and 10 for example? Like passing it an array of indexes to ignore and it will skip over those array elements from the JSON when populating the table. Essentially, something similar to when we use 'column' to specify which data to create and their each of their column name, but for rows.

kthorngren

There is nothing built into Datatables to ignore elements within the data array. You will need to remove them before applying to Datatables. If using ajax you can use ajax.dataSrc as a function to iterate the JSON data or other Javascript methods to remove the rows.

The most efficient way would be to remove those rows from the data at the server before sending the JSON response.

If you just want to filter the initial display but have those rows as part of the table you could use search.search or searchCols.

Kevin

nya13

I see. Yeah, the SQL server needs to have the first row of the result set with the column names as we use that first row to auto-generate the data needed for the 'columns' property and pass that to the DataTables({ 'columns': COLUMN_DATA_ARRAY }) method. We definitely do not want some rows to be populated and hidden from display. I haven't looked at the DataTables API code, but I assume it does a .forEach(data, rowIndex) loop to iterate across each element of the JSON array. Wished we could pass it say 'ignoredRowIndexesArray': [0, 4, 10, 66] and it would check on each pass if the rowIndex is in that array and bypass it. Saves me from either mutating the original array or creating a filtered copy.

At the same time, if it had a parameter say,'rowIndexesArray' : [8, 9, 22, 34], then it would just try to look at those rows (and also take into consideration what is in ignoredRowIndexesArray). There coul also be a rowFilterCallbackfunction we could pass that could do any kind of filtering (like maybe do the include row indexes and ignored row indexes check there, on the outside...) again each data of the JSON before adding them to the DataTables. But hey, I'll just make a wrapper function on top of the DataTable() call to have those functionalities!

I guess another work around would be to run the.remove() DataTables method against each.rows().nodes()that I don't want to exist.

nya13

Oh oh, there is a rowCallback. Will check that out!

nya13

ahhh.. looks like rowCallback() does not allow us to prevent a row from being added. So close.

kthorngren

If you know the indexes you could use rows().remove() and pass in an array of indexes as described in the row-selector docs. Do this in initComplete. This will happen after the Datatable has initialized and initially displayed the rows. Might be quick enough you would not notice but then again it might not be.

I'm guessing you are using jQuery ajax() to fetch the data to get the columns. Maybe one of the techniques in this article will help to remove the array elements you want using the array index. Use this in the success function before initializing Datatables.

Using one of the callbacks, like rowCallback is probably not going to work well.

Maybe you can still remove the rows you don't want server side. Return the column names, etc in a different object separate from the row data.

Datatables is open source so you could add the feature to drop the array indexes specified. I don't remember anyone asking for a feature to drop rows by index before. You can ask @allan about creating this feature.

Kevin

nya13

heh, just a silly worse case scenario, but if I had 100000 rows and just wanted to load 1 row, I would probably see the screen flicker with all the awesome secret data. The SQL server does a pivot in order to create a result set with dynamic columns and the C# processor returns the result set to the client with the the column names as the property key for each data of the JSON, but can also set those exact column names on the first row the result set (because the C# processor by default trims the column names/aliases and makes uppercase..arrg....) Thank you for all your inputs @kthorngren!

@allan, please, think about adding this feature to "filter" rows before they are even added. This will save us from pre-processing (slice, splice, copy, etc...) the data array, thus saving system memory and processing power. Just a general draft idea of how the filter would look like, but you guys are the owners so...

DataTable() would have a parameter called filterCallback set as undefined.

For each row, there would be an if condition that would look something like:

        // jsonDataArray = what you pass to the 'data' property.
        jsonDataArray.forEach(function (jsonData, rowIndex) {
            // Or just !filterCallback to be lazy.  And I'm passing an object/data in the filterCallback function, as I think we should all be doing...
            // filterCallback is a callback that the user can customize as they see fit and will have to return true or false.  If true, process the jsonData as normal.  Otherwise, ignore it completely.
            if ((filterCallback === undefined) || filterCallback({ 'jsonData': jsonData, 'rowIndex': rowIndex })) {
                // >> nodes()
                // NODES_ARRAY.push(ROW_ELEMENT);
            }
        });

kthorngren

but if I had 100000 rows and just wanted to load 1 row

Thats why its more efficient to do this server side. Maybe setup your SQL query to return only the desired rows. Or remove them elsewhere. It's unclear how you know which rows to remove and how the client side gets this info. Doing this server side will reduce the size of the JSON response.

Kevin

nya13

It was just a silly example. Normal case is just 1 row to remove out of 1 million. Mutating my original array in this case is acceptable, so that is what I will do a .shift() right after I am done auto-generating the 'columns' data array. Thank you!