Search by column doesn't care for column number

MadBoyEvo

Link to test case:

https://codepen.io/MadBoyEvo/pen/ZEBxbGw

Description of problem:

In a chart there is this code for events (line 350):

events: {
    click: function (event, chartContext, config) {

    },
    dataPointSelection: function (event, chartContext, config) {

    },
    markerClick: function (event, chartContext, { seriesIndex, dataPointIndex, config }) {
        chartEventMarkerClick('IDTable', 0, chartContext.opts, dataPointIndex, seriesIndex);
    }
}

In case of line charts it's based on line marker. When you press line the function gets executed. The function that it's executing takes columnid. In normal case I would choose proper column (normally it should be column nr 3) which has dates that are used on Y AXIS. However what I noticed that even if I pass any other columnid to the code below it doesn't matter. It does find values somehow

table.column(columnid).search("^" + columnValue + "$", true, false, true).draw();

I disabled smart search (I guess?) but it still finds things. I expected that if ColumnValue is year 2009 it would try to search it in column 0 and fail because there's no value that matches. What I am doing wrong? I also tried search by columns, but it didn't help

table.columns(columnid).search("^" + columnValue + "$", true, false, true).draw();

<script type="text/javascript">
    function chartEventMarkerClick(tableid, columnid, config, dataPointIndex, seriesIndex) {
        if (['line'].includes(config.chart.type)) {
            // line charts are supported in markerClick
            var highlightValue = escapeRegExp(config.series[seriesIndex].data[dataPointIndex])
            var columnValue = escapeRegExp(config.xaxis.categories[dataPointIndex])

            // if value is the same we clicked on before, we clear the search, if not we continue
            if (dataTablesChartsEvents[tableid] === highlightValue) {
                clearTableSearch(tableid, columnid);
                return true;
            }
            var table = $('#' + tableid).DataTable();
            if (columnValue != '') {
                dataTablesFindMatch(table, tableid, columnid, columnValue, highlightValue)
            } else {
                clearTableSearch(tableid, columnid);
            }
            if (table.page.info().recordsDisplay == 0) {
                table.columns(columnid).search('').draw();
            }
        }
    }
    function dataTablesFindMatch(table, tableid, columnid, columnValue, highlightValue) {
        //console.log('columnValue:' + columnValue + ' highlightValue:' + highlightValue + ' tableid:' + tableid + ' currentValue:' + dataTablesChartsEvents[tableid]);
        //var table = $('#' + tableid).DataTable();
        table.column(columnid).search("^" + columnValue + "$", true, false, true).draw();
        $('#' + tableid + ' td').removeClass('highlight');
        if (highlightValue) {
            dataTablesChartsEvents[tableid] = highlightValue;
        } else {
            dataTablesChartsEvents[tableid] = undefined;
        }
        table.draw(); // Run the search plugin
    }
    function clearTableSearch(tableid, columnid) {
        var table = $('#' + tableid).DataTable();
        table.columns(columnid).search('').draw();
        dataTablesChartsEvents[tableid] = undefined;
        table.draw();
        $('#' + tableid + ' td').removeClass('highlight');
        table.draw();
    }
</script>

Any tips why this doesn't work how I think it should work?

kthorngren

Looks like there are more searches performed than you are expecting. Maybe due to your other thread. Might be due to something else.

I don't have a way to save the codepen code. I added this search event to see every time the table is searched:

$('#IDTable').on( 'search.dt', function () {
  var table = $('#IDTable').DataTable();
   console.log( 'search event' );
  console.log('global:', table.search());
  console.log('col 0:', table.column(0).search());
  console.log('col 1:', table.column(1).search());
  console.log('col 2:', table.column(2).search());
  console.log('col 3:', table.column(3).search());
} );

I added this console log just below where you highlight the found cell in the search plugin:

console.log('Searh plugin - highlight:', dataTablesChartsEvents['IDTable'])

Clicking on one of the points on the line results in this output:

search event
global: 
col 0: ^2009$
col 1: 
col 2: 
col 3: 
search event
global: 
col 0: ^2009$
col 1: 
col 2: 
col 3: 
columnValue 2009
search event
global: 
col 0: ^2009$
col 1: 
col 2: 
col 3: 
40 Service Desk 1,1,0,2001  0
41 Service Desk 2,20,15,2002  1
42 Service Desk 3,40,19,2003  2
43 Service Desk 4,15,25,2004  3
44 Service Desk 5,10,19,2005  4
45 Service Desk 6,45,45,2006  5
46 Service Desk 7,18,15,2007  6
Searh plugin - highlight: 18
47 Service Desk 8,60,50,2008  7
48 Service Desk 9,18,5,2009  8
Searh plugin - highlight: 18
49 Service Desk 0,9,2,2010  9
search event
global: 
col 0: 
col 1: 
col 2: 
col 3: 
50 Service Desk 1,1,0,2001  0
51 Service Desk 2,20,15,2002  1
52 Service Desk 3,40,19,2003  2
53 Service Desk 4,15,25,2004  3
54 Service Desk 5,10,19,2005  4
55 Service Desk 6,45,45,2006  5
56 Service Desk 7,18,15,2007  6
Searh plugin - highlight: 18
57 Service Desk 8,60,50,2008  7
58 Service Desk 9,18,5,2009  8
Searh plugin - highlight: 18
59 Service Desk 0,9,2,2010  9
search event
global: 
col 0: 
col 1: 
col 2: 
col 3: 

You can see multiple searches (5 in this output) happen just for one click on the line graph. At first the column 0 search is active then its cleared. You can see the search plugin is executed multiple times due to draw() being called.

It would take a lot of time to debug your code to find out why there are so many searches taking place when clicking a line point just once. More importantly to find out why the column 0 search is being cleared.

Looks like the table.column(columnid).search("^" + columnValue + "$", true, false, true).draw(); search is working but the column 0 search is cleared at some later time.

Kevin

MadBoyEvo

My problem is I don't see how it's getting cleared. My function is pretty straightforward.

I believe it's connected to the other thread I opened: https://datatables.net/forums/discussion/67260/search-executed-multiple-times#latest

My search command is executed only once when the bar is pressed.

function dataTablesFindMatch(table, tableid, columnid, columnValue, highlightValue) {
                //console.log('columnValue:' + columnValue + ' highlightValue:' + highlightValue + ' tableid:' + tableid + ' currentValue:' + dataTablesChartsEvents[tableid]);
                //var table = $('#' + tableid).DataTable();
                table.column(columnid).search("^" + columnValue + "$", true, false, true).draw();
                $('#' + tableid + ' td').removeClass('highlight');
                if (highlightValue) {
                    dataTablesChartsEvents[tableid] = highlightValue;
                } else {
                    dataTablesChartsEvents[tableid] = undefined;
                }
                table.draw(); // Run the search plugin
            }

But what I believe this search extension is called every time .draw() is executed.

$.fn.dataTable.ext.search.push(
    function (settings, searchData, index, rowData, counter) {

        if (settings.nTable.id !== 'NewIDtoSearchInChart') {
            return true;
        }
        if (dataTablesChartsEvents['NewIDtoSearchInChart'] === undefined) {
            return false;
        }
        //console.log("Data for table: " + settings.nTable.id + ' finding id ' + dataTablesChartsEvents['NewIDtoSearchInChart'] + ' searching for ' + searchData);
        $('#' + 'NewIDtoSearchInChart' + ' td').removeClass('highlight');
        console.log(count++ + ' ' + rowData + '  ' + index);
        if (searchData.includes(dataTablesChartsEvents['NewIDtoSearchInChart'])) {

            // Get column index of matched value
            var colIndex = searchData.indexOf(dataTablesChartsEvents['NewIDtoSearchInChart']);

            // Get Datatable API
            var table = new $.fn.dataTable.Api(settings);

            // Get cell().node() for matched value
            var cell = table.cell(index, colIndex).node();

            // highlight the cell
            $(cell).addClass('highlight')

            return true;
        }
        return false;
    }
);

So when I do search using

 table.column(columnid).search("^" + columnValue + "$", true, false, true).draw();

It triggers once, but then when this code gets executed

                $('#' + tableid + ' td').removeClass('highlight');
                if (highlightValue) {
                    dataTablesChartsEvents[tableid] = highlightValue;
                } else {
                    dataTablesChartsEvents[tableid] = undefined;
                }
                table.draw(); // Run the search plugin

it triggers two times. So this extension triggers every time .draw() is called but also when something else happens, and I am getting a hard time understanding how that extension works and how to kind of queue the search so that first draw() doesn't trigger clearance of the search. After all, it seems to work correctly for bar charts and another type of charts (still executing more times than what I expect it to - as reported in https://datatables.net/forums/discussion/67260/search-executed-multiple-times ) - but with line charts, it's also clearing the search which I don't know where that would happen.

So someone with knowledge of the search extension would need to chime in and explain possible options here.

MadBoyEvo

Ok, I believe I understand how it works. The search extension is executed for every rowData. When I limited the output to 1 row, it executes multiple times with same data - which is a bit weird, maybe a bug. But if I just do

            function dataTablesFindMatch(table, tableid, columnid, columnValue, highlightValue) {
                if (highlightValue) {
                    dataTablesChartsEvents[tableid] = highlightValue;
                } else {
                    dataTablesChartsEvents[tableid] = undefined;
                }
                table.draw(); // Run the search plugin
            }

It executes 4 times (amount of rows) displaying each row one time. So if I move all code into search extension and do all the search there it should work properly.

            <script type="text/javascript">    $.fn.dataTable.ext.search.push(
                    function (settings, searchData, index, rowData, counter) {

                        if (settings.nTable.id !== 'NewIDtoSearchInChart') {
                            return true;
                        }
                        if (dataTablesChartsEvents['NewIDtoSearchInChart'] === undefined) {
                            return true;
                        }
                        //console.log("Data for table: " + settings.nTable.id + ' finding id ' + dataTablesChartsEvents['NewIDtoSearchInChart'] + ' searching for ' + searchData);
                        $('#' + 'NewIDtoSearchInChart' + ' td').removeClass('highlight');

                        if (searchData.includes(dataTablesChartsEvents['NewIDtoSearchInChart'])) {
                            console.log(count++ + ' ' + rowData + '  ' + index);
                            // Get column index of matched value
                            var colIndex = searchData.indexOf(dataTablesChartsEvents['NewIDtoSearchInChart']);

                            // Get Datatable API
                            var table = new $.fn.dataTable.Api(settings);

                            // Get cell().node() for matched value
                            var cell = table.cell(index, colIndex).node();

                            // highlight the cell
                            $(cell).addClass('highlight')

                            return true;
                        }
                        return false;
                    }
                );</script> 

I just need to do dual search, first to limit for the row I want, and then to highlight value.

kthorngren

The search plugin is executed after all the builtin Datatables searches are run. So it executes over the non-filtered rows. It runs anytime there is a draw event like paging, sorting, searching or calling draw(). The function in the plugin is called once for each row.

So when I do search using

table.column(columnid).search("^" + columnValue + "$", true, false, true).draw();

It triggers once, but then when this code gets executed

$('#' + tableid + ' td').removeClass('highlight');
if (highlightValue) {
    dataTablesChartsEvents[tableid] = highlightValue;
} else {
    dataTablesChartsEvents[tableid] = undefined;
}
table.draw(); // Run the search plugin

it triggers two times

In this case just call draw() once. Remove it from the column().search() and call it at the end.

My problem is I don't see how it's getting cleared. My function is pretty straightforward.

I would start by using the browser's debugger and placing breakpoints on all search statements to get an idea of the code flow. Hopefully doing this will allow you to see where/why the column().search() is being cleared.

Kevin

kthorngren

I had a few minutes so used the debugger with breakpoints on the column().search() statements.

When clicking a data point the column().search() in line 217 is executed. Then something calls chartEventMarkerClick() and the search in line 210 is called clearing the column search.

Kevin

MadBoyEvo

I've worked it out.

https://codepen.io/MadBoyEvo/pen/VwmXdaR

I've totally removed searching using the "standard" way. My understanding is that I can't mix search with search extension. So I've created a function that does both:

                $.fn.dataTable.ext.search.push(
                    function (settings, searchData, index, rowData, counter) {
                        return dataTablesSearchExtension('IDTable', settings, searchData, index, rowData, counter, true);
                    }
                );

And the function itself. When limitRow is set to true it will require a match for column value plus highlight value to.

}
function dataTablesSearchExtension(tableid, settings, searchData, index, rowData, counter, limitRow) {
    if (settings.nTable.id !== tableid) {
        return true;
    }
    if (dataTablesChartsEvents[tableid] === undefined) {
        return true;
    }
    // Get Datatable API
    var table = new $.fn.dataTable.Api(settings);
    var columnid = dataTablesChartsEvents[tableid].columnid

    if (limitRow) {
        if (searchData[columnid] === dataTablesChartsEvents[tableid].columnValue && searchData.includes(dataTablesChartsEvents[tableid].highlightValue)) {
            // Get column index of matched value
            var colIndexHighlight = searchData.indexOf(dataTablesChartsEvents[tableid].highlightValue);
            // Get cell().node() for matched value
            var cell = table.cell(index, colIndexHighlight).node();
            // highlight the cell
            $(cell).addClass('highlight');
            return true;
        }
    } else {
        //console.log("Data for table: " + settings.nTable.id + ' finding id ' + dataTablesChartsEvents['$DataTableID'].highlightValue + ' searching for ' + searchData);
        //console.log(count++ + ' ' + rowData + '  ' + index + ' ' + dataTablesChartsEvents[tableid].highlightValue);
        if (searchData[columnid].includes(dataTablesChartsEvents[tableid].highlightValue)) {
            // Get column index of matched value
            var colIndex = searchData.indexOf(dataTablesChartsEvents[tableid].highlightValue);
            // Get cell().node() for matched value
            var cell = table.cell(index, colIndex).node();
            // highlight the cell
            $(cell).addClass('highlight');
            return true;
        }
    }
    return false;
}

kthorngren

My understanding is that I can't mix search with search extension.

You can use both. As I mentioned your code was clearing the column search so it looked like the search wasn't working correctly. Glad you got it sorted out.

Kevin