Correct auto filtering issue with data merged from more than one column

Dirk Fincley

You can "render" data from 2 columns into one like for example a firstname and a surname see the js snippet below.

...

        {
            "data": "agent.surname",
            "render": function ( data, type, row ) 
            {
                return row.agent.first_name +' '+ row.agent.surname;
            }
        },

...

However, if you use this the data tables "auto filtering logic" (shown below) it only shows data from ONE of the columns (in this case the surname).
...

             "initComplete": function () {
            this.api().columns().every( function () {
                var column = this;
                var select = $('<select><option value=""></option></select>')
                  .appendTo( $(column.footer()).empty() )
                  .on( 'change', function () {
                    var val = $.fn.dataTable.util.escapeRegex(
                        $(this).val()
                    );

                    column
                        .search( val ? '^'+val+'$' : '', true, false )
                        .draw();
                  } );

                column.data().unique().sort().each( function ( d, j ) {
                  select.append( '<option value="'+d+'">'+d+'</option>' )
                } );
            } );
        },

...

The code below shows "column.data().unique().sort().each( function ( d, j ) {" has been changed to " column.data().cells( null, column.index() ).render( 'display' ).unique().sort().each( function ( d, j ) {".

This makes it use the contents of the columns AFTER rendering and so in this case I see the first name then the surname for each unique entry in the select filter box at the bottom of the agent column. All other columns work as before as they were not rendered.

var table = $('#agent').DataTable( {

             "initComplete": function () {
            this.api().columns().every( function () {
                var column = this;
                var select = $('<select><option value=""></option></select>')
                  .appendTo( $(column.footer()).empty() )
                  .on( 'change', function () {
                    var val = $.fn.dataTable.util.escapeRegex(
                        $(this).val()
                    );

                    column
                        .search( val ? '^'+val+'$' : '', true, false )
                        .draw();
                  } );

                column.data().cells( null, column.index() ).render( 'display' ).unique().sort().each( function ( d, j ) {
                  select.append( '<option value="'+d+'">'+d+'</option>' )
                } );
            } );
        },

...

Dirk Fincley

Note this solution was given by Allan not me.

allan

That's for posting this up. I've been thinking about a way to tag posts or create a wiki or something to get such snippets higher visibility over general forum posts. Not sure on the best way forward yet, but I'll be looking into the options.

Allan

Dirk Fincley

Great idea. It would be worth indexing the links in some way. Maybe by using a Datatables application and a relational database? That way a single link could be referenced by a number of keywords?

It should also hopefully reduce the number of questions which you get. Developers are naturally lazy and tend to learn best by seeing working examples. I certainly do. The problem can be finding decent examples which match real life problems.

allan

The problem with just using DataTables is that it will quickly become overwhelming. Categorisation and linking will likely be key and probably searching.

Allan

Dirk Fincley

Indeed. I generally manage to find most relevant threads just by a Google search to be honest. It may be best to leave it like that.