Filtering out identical rows

edd_xyz

Hi,
I very much a javascript newbie and am really impressed by how quickly I've managed to get a very useful, functional tool using DataTables. So I'm a big fan.
I am using the colVis plugin to allow the user to dynamically hide / show columns. The main purpose of this appeared to me to be to allow the user to get an overview of the information and then to drill down where they want to (by adding in columns to get additional data). For this to work, I need a feature that doesn't appear to be available in colVis - that is to hide any duplicated row.

ie. If you had a table giving a list of car sales with three columns: car model, shop location and customer name, I want to be able to hide the customer name and shop location columns and get a list of distinct rows showing me which car models we've had at least 1 sale for.

I've managed to do this myself using the following bit of code within the $(document).ready function (my datatables object is $('#my_table):

        // When I'm doing this, I'm not updating the 'showing x to y of z entries' text, so that becomes incorrect
        $('#my_table').on( 'draw.dt', function() {
            var seen = {};
            $('table tr').each(function() {
                var txt = $(this).text();
                if (seen[txt])
                    $(this).remove();
                else
                    seen[txt] = true;
            });
        });

So when the table is redrawn, the above gets triggered and duplicate rows are removed.
As my comment indicates, the issue is that this gets applied after datatables has redrawn the table. So if, for example, I am displaying 20 rows per page, and they all happen to be duplicates of the same row, the bit of code above happily gets rid of the duplicates but then I'm left with a page showing just a single row, rather than the 20 distinct rows that I want.
In addition, the fairly useful comment (which gets calculated before I get rid of rows) that states 'Showing x to y of z entries' becomes completely wrong.

Any help / tips on what I could do to get this working would be very much appreciated!

allan

I think you might be best using a custom filter to perform this kind of action.

Allan

edd_xyz

Allan,

Many thanks. I got this working perfectly last night, thanks to that pointer.
There's nothing special here, but just in case this helps someone else (note my datatables instance is called 'myTable'):

        var rows = []
        $.fn.dataTable.ext.search.push(
            function( settings, searchData, index, rowData, counter ) {
                // On a new search, the counter is zero.  This is my opportunity to clear the array
                if (counter == 0)
                {
                    rows = [];
                }

                // To work out if two rows are duplicates, I need to know which columns are hidden
                num_columns = settings.aoColumns.length;
                var visible_columns = [];
                for (var index=0; index<num_columns; index++)
                {
                    if (myTable.column(index).visible() == true)
                    {
                        visible_columns.push(index);
                    }
                }

                // I want row to be a combination of the visible columns
                var row = '';
                for (var index=0; index<visible_columns.length; index++)
                {
                    row += ' ' + rowData[visible_columns[index]];
                }

                // If we've seen the row before (already in the array), filter it out.
                // Otherwise, return true and add it to the array
                if ($.inArray(row,rows) > -1)
                {
                    return false;
                } else {
                    rows.push(row);
                    return true;
                }
            }

        );

One issue is that now the code is correctly sifting through thousands of rows rather than just the few that made it to the screen, so it's much slower.
I've improved that situation by using the fnFilterOnReturn plugin, so that the search is only done on a press of enter rather than every key press.

edd_xyz

One more comment on this - I realised I had another problem that seems fairly common - that my searches were matching against hidden columns. Although I guess it must slow the filtering down, I solved that problem in the same way. In my custom filter above, I'm already building up a string comprised of the visible columns. I now just have an extra bit that grabs the search string, breaks it up on spaces and makes sure each search term matches the visible row sting.

So the last bit of the code above becomes:

'''

                // If we've seen the row before (already in the array), filter it out.
                // Otherwise, return true and add it to the array
                if ($.inArray(row,rows) > -1)
                {
                    return false;
                } else {

                    // Don't add the row until we're sure the search string only matches the VISIBLE columns
                    // (This isn't functionality that DataTables gives by default)

                   // Need to split the search string on space, and then ensure every bit matches the row
                   var  search_strings = $(".dataTables_filter input").val().replace("/\\/g","\\\\").split(" ");
                    for (var i = 0; i < search_strings.length; i++) {
                        var regex = new RegExp(search_strings[i], "i");
                        if (regex.test(row) == false) {
                            return false;
                        }

                    }
                    rows.push(row);
                    return true;

'''