Individual column searching not working.

MickB

Hi,

Can anyone see what is wrong with this?
Following the example from : https://datatables.net/examples/api/multi_filter_select.html

The selects are appearing but the search is not doing anything. No errors in the console.

Thanks,

Mick

debug code: okixan

$(document).ready(function () {

            var table = $('#lookupTable').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().unique().sort().each( function ( d, j ) {
                            select.append( '<option value="'+d+'">'+d+'</option>' )
                        } );
                    } );
                },


                responsive: true,

                @if ($pagination != 1)
                    "paging":   false,
                @endif


                @if($show_export_buttons === 1)

                    dom: 'Bfrtip',
                    buttons: [
                        'copy', 'csv', 'excel', 'pdf', 'print'
                    ],
                @endif

                "processing": true,
                "pageLength": 25,
                "bFilter":   false,

                "columnDefs": [
                    {
                        "targets": [ -1 ],
                        "visible": false,
                        "searchable": false
                    }

                ],


                //This adds the Bootstrap alert class, if there is one in the last column
                "createdRow": function( row, data, dataIndex ) {

                    /*console.log(data);*/

                    if ( data[data.length-1] != '' ) {
                        $(row).addClass( data[data.length-1] );
                    }
                }




            });

            new $.fn.dataTable.FixedHeader( table );
            $('#loader').hide();
            $('#lookupTable').show();
        });

allan

"bFilter": false,

Search has been disabled. Remove that and it should work.

Allan

MickB

Yes, thanks.

MickB

Is there an example where the selects have been moved to the top?

MickB

Got it:

var select = $('<select onclick="stopPropagation(event);"><option value=""></option></select>')

MickB

Ahhh, now I need to filter the other selects, after I have applied a filter.

I will have a go at that.

MickB

This doesn't look this easy, has anyone done this?

When a value is selected, the rows are filtered. I then want to update the other selects, so that they reflect the filtered columns.

Does that make sense?

Eg,

Customer No	Name
CHU027	A
CHU027	B
WAH001	C
WAH001	D

When CHU027 is selected, only A and B are shown in the select for the Name filter.

Happy to move this to paid support.

Mick

allan

I wrote this example a while back showing how the filters can be updated when an item is selected in another filter: http://live.datatables.net/gejojiqu/1/edit .

Allan

MickB

Thats brilliant Allan, thanks.

Have a great weekend.

Mick

MickB

Hi Allan,

I have this working now, but I have a slight problem with the table headers. My filters are at the top and this is affecting the column name spacing.

https://ibb.co/m1OA4a

Any idea where I can start with this? Is just adding right and left padding to th the best way?

This is where I add them:

  <thead>
    <tr>
        {{--This is the column headers--}}
        @foreach($neatHeaders as $neatHeader )
            <th>
                @if($neatHeader != 'Row Highlight') {{--Skip this one, it's just used to add--}}
                {{ $neatHeader }}
                @endif

            </th>
        @endforeach

    </tr>

    @if ($filters == 1)
        <tr>
            {{--This is the column filters--}}
            @foreach($neatHeaders as~~~~ $neatHeader )
                <th>
                    @if($neatHeader != 'Row Highlight') {{--Skip this one, it's just used to add--}}
                    {{ $neatHeader }}
                    @endif

                </th>
            @endforeach

        </tr>
    @endif

    </thead>

allan

Try using the orderCellsTop option.

Allan

nelsondcosta

Hi allan, I saw your code at http://live.datatables.net/gejojiqu/1/edit

Any sugestions how can I use this with sql server from behind?

     $(document).ready(function() {

        $('#example').DataTable( {

            //Esta função a baixo deixa copia, exportar para .csv .excel .pdf e print directo.
            //https://datatables.net/reference/option/dom
            dom: 'Blfrtip', //MANTEM A PAGINAÇÃO
            buttons: [
                'copy', 'csv', 'excel', 'pdf', 'print'
            ],

            "processing": true,
            //"serverSide": true, ao desativar esta opção o filtro detecta tudo
            "serverSide": false,
            "ajax": {
                "url": "server.php",
                "type": "POST"
            },
            "columns": [
                {
                    "className":      'details-control',
                    "orderable":      false,
                    "data":           null,
                    "defaultContent": ''
                },
                { "data": "MATRICULA" },
                { "data": "MARCA" },
                { "data": "MODELO" },
                { "data": "MOTORIZACAO" },
                { "data": "IVA" },
                { "data": "EQUIPAMENTO" },
                { "data": "CARROCARIA" },
                { "data": "TESTE" }
            ],
            "order": [[1, 'asc']],


            initComplete: function ()
            {   
                this.api().columns([2,3,4,5,6,7]).every( function () // Colunas a mostrar
                {
                    var column = this;
                    var select = $('<select><option value=""></option></select>')
                        //OLD SCRIPT .appendTo( $(column.footer()).empty() )  // Remover o .empty() faz aparecer nome da <td> em baixo | column.header Select passa para top
                        .appendTo( $("#example thead tr:eq(0) th").eq(column.index()).empty() )  // Este nova linha, faz com que o search fique no head da tabela sem danificar o organiador de preço
                        .on( 'change', function () {var val = $.fn.dataTable.util.escapeRegex($(this).val());
                            column
                                .search(val)
                                .draw();
                    });
                    column.data().unique().sort().each( function ( d, j )
                    {
                        select.append( '<option value="'+d+'">'+d+'</option>' );
                    } );

Thanks in advance

colin

As serverSide is false, all the data will be in the table from the start. So, you're using initComplete already, you just need to add the

table.on( 'draw', function () {
    buildSelect( table );
  } );

code in there too, to ensure the table is updated with each draw.

Colin

nelsondcosta

Hi colin, thanks for quick help.

But in not getting where I have to replace your code in my code

colin

There you go : http://live.datatables.net/deboparo/1/edit

Cheers,

Colin

nelsondcosta

Hi Colin, AMAZING!
I put all my code in to trash and use your code as base and everything its working very well.
The only thing I detected for now is, when I create a limite on wich colunm I want the select filter displays, table.columns([2,3,4,5,6,7])

The select filter doesnt work well.

function buildSelect(table) {
        table.columns().every(function() { // table.columns([2,3,4,5,6,7]) ao colocar as colunas que quero deixa de ter smart filter
            var column = table.column(this, {
                search: 'applied'
            });
            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();
                });

FORGET THIS POST, FIXED!
I just selected the wrong columns.

Once again Colin, thanks for help me out

nelsondcosta

Hi Colin, how are you?

Its hard to doa multiple filter?
I want to select option A and B and the next filters give me just the information about A and B
Like the post above

kthorngren

Are you looking for something like this using select2?
http://live.datatables.net/xehexoye/1/edit

Kevin

nelsondcosta

Yap I already tried to adapt my code using that link.
And I failed hard ;-)

colin

I'm not too clear what the problem is, or whether it's been fixed If it's still an issue, please can you give more details on your requirements.

Colin

nelsondcosta

Hi colin,

Well you helped me out doing something like this = http://live.datatables.net/deboparo/1/edit

And it work very well

My ideia is, being able to select more options per filter like Accountant and Developer
and the others filters only show up the options left.

Im not sure if i explained right

I tried adapt using this = http://live.datatables.net/xehexoye/1/edit
And I just fail hard in my code

colin

Your last test case looks good to me - the select2 is working as expected and is filtering correctly! Can you explicitly say what it's doing wrong, and what you would expect it to do instead, please.

Colin

nelsondcosta

Hi Colin I will make this post in 2 parts.
1- my code right now
2- the abomination I tried

1- my code
`function format ( d ) {

return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
    '<tr>'+
        '<td><b>'+d.Matricula+'</b>&nbsp;&nbsp;&nbsp;<b>Localização:</b> em testes'+
        '<br>'+d.Marca+'&nbsp;|&nbsp;'+d.Modelo+'&nbsp;|&nbsp;'+d.Motorização+'&nbsp;|&nbsp;'+d.Equipamento+'&nbsp;|&nbsp;'+d.Caixa+''+
        '<br><b>Novo:</b>'+d.preo_novo+'&nbsp;&nbsp;&nbsp;<b>Recomendado:</b> '+d.PreçoRecomendado+'<br>'+
        '<td><b>Cilindrada:</b>&nbsp;&nbsp;&nbsp;'+d.Cilindrada+'<br>'+
        '<b>Utilizador:</b>&nbsp;&nbsp;&nbsp;'+d.NomeUtilizador+'<br>'+
        '<b>Iva:</b>&nbsp;&nbsp;&nbsp;'+d.IVA+'<br>'+
        '<b>PM:</b>&nbsp;&nbsp;&nbsp;'+d.PM+'<br>'+
        '<b>Data da Matricula:</b>&nbsp;&nbsp;&nbsp;'+d.DataMatricula_info+'</td>'+
        '<td width="100px"><b>Dias Stock:</b>&nbsp;&nbsp;&nbsp;'+d.dias_stock+'<br>'+
        '<b>Origem:</b>&nbsp;&nbsp;&nbsp;'+d.OrigemViatura+'<br>'+
        '<b>Carrocaria:</b>&nbsp;&nbsp;&nbsp;'+d.Carroçaria+'<br>'+
        '<b>Combustivel:</b>&nbsp;&nbsp;&nbsp;'+d.Combustivel+'<br>'+
        '<b>Caixa:</b>&nbsp;&nbsp;&nbsp;'+d.Caixa+'</td>'+
        '<td width="500px"><b>Extras:</b>&nbsp;'+d.Extras+'</td>'+
    '</tr>'+
'</table>'
;}  


$(document).ready(function() {  
function buildSelect(table) {
table.columns([2,3,4,5,6,7,8,11,12,13]).every(function() { 
        var column = table.column(this, {
            search: 'applied'
        });
        var select = $('<select><option value=""></option></select>')
            .appendTo( $("#example thead tr:eq(0) th").eq(column.index()).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>');
        });
        var currSearch = column.search();
        if (currSearch) {
            select.val(currSearch.substring(1, currSearch.length - 1));
        }
    });
}       


var table = $('#example').DataTable({
    language: {"url": "configsys/css/pt-pt.json"},
    dom: 'Blfrtip',
    buttons: ['copy', 'csv', 'excel', 'pdf', 'print'],
    lengthMenu: [[10, 5, 25, 50, -1], [10, 5, 25, 50, "Todos"]], 
    paging: true,
    processing: true,
    serverSide: false,
    ajax: "configsys/phps/server.php",
    type: "POST", 
    columns: [
            {
            "className":      'details-control',
            "orderable":      false,
            "data":           null,
            "defaultContent": '<button>Infos</button>'
            },
        {"data": "Matricula"},
        {"data": "DataMatricula"},
        {"data": "Modelo"},
        {"data": "Motorização"},
        {"data": "Carroçaria"},
        {"data": "Equipamento"},
        {"data": "Combustivel"},
        {"data": "Cor"},
        {"data": "Kilometros"},
        {"data": "PreçoRecomendado"},
        {"data": "Caixa"},
        {"data": "TipoViatura"},
        {"data": "Extras"}

    ],
    "order": [[1, 'asc']],


    initComplete: function() {
        buildSelect(table);
        table.on('draw', function() {
            buildSelect(table);
        });     


    $('#example tbody').on( 'click', 'button', function () {
        var data = table.row( $(this).parents('tr') ).data();
        alert( data[0] +"'s salary is: "+ data[ "Cor" ] );
    } );    


    $('#example tbody').on('click', 'td.details-control', function () {
    var tr = $(this).closest('tr');
    var row = table.row( tr );

    if ( row.child.isShown() ) {
        row.child.hide();
        tr.removeClass('shown');
    }
    else {
        row.child( format(row.data()) ).show();
        tr.addClass('shown');
    }
    } );
    }
});

});`

nelsondcosta

2 - the abomination

`
function format ( d ) {
return '

'+ ''+ ''+ ''+ ''+ ''+ '

'+d.Matricula+'   Localização: em testes'+ ' '+d.Marca+' | '+d.Modelo+' | '+d.Motorização+' | '+d.Equipamento+' | '+d.Caixa+''+ ' Novo:'+d.preo_novo+'   Recomendado: '+d.PreçoRecomendado+' '+ '

Cilindrada:   '+d.Cilindrada+' '+ 'Utilizador:   '+d.NomeUtilizador+' '+ 'Iva:   '+d.IVA+' '+ 'PM:   '+d.PM+' '+ 'Data da Matricula:   '+d.DataMatricula_info+'

Dias Stock:   '+d.dias_stock+' '+ 'Origem:   '+d.OrigemViatura+' '+ 'Carrocaria:   '+d.Carroçaria+' '+ 'Combustivel:   '+d.Combustivel+' '+ 'Caixa:   '+d.Caixa+'

Extras: '+d.Extras+'

'
;}

$(document).ready(function() {
function buildSelect(table) {
table.columns([2,3,4,5,6,7,8,11,12,13]).every(function() { 
        var column = table.column(this, {
            // var column = this;
            search: 'applied'
        });
        //var select = $('<select><option value=""></option></select>')
        var select = $('<select id="' + title + '" class="select2" ></select>')
            .appendTo( $("#example thead tr:eq(0) th").eq(column.index()).empty() ) 
            //.appendTo( $(column.footer()).empty() )
            .on('change', function() {

                //var val = $.fn.dataTable.util.escapeRegex(
                //    $(this).val()
                //);
                var val = $.map( $(this).select2('val'), function( value, key ) {
                    return value.text ? '^' + $.fn.dataTable.util.escapeRegex(value.text) + '$' : null;
                 });

                                      //if no data selected use ""
                  if (data.length === 0) {
                    data = [""];
                  }

                  //join array into string with regex or (|)
                  var val = data.join('|');

                //column
                  //  .search(val ? '^' + val + '$' : '', true, false)
                   // .draw();
                          //search for the option(s) selected
                          column
                        .search( val ? val : '', true, false )
                        .draw();
                } );
            });

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

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

        var currSearch = column.search();
        if (currSearch) {
            select.val(currSearch.substring(1, currSearch.length - 1));
        }
    });
}       


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

    language: {"url": "configsys/css/pt-pt.json"},
    dom: 'Blfrtip', 
    buttons: ['copy', 'csv', 'excel', 'pdf', 'print'], 
    lengthMenu: [[10, 5, 25, 50, -1], [10, 5, 25, 50, "Todos"]],    
    paging: true,
    processing: true,
    serverSide: false,
    ajax: "configsys/phps/server.php",  
    type: "POST", 
    columns: [

            {
            "className":      'details-control',
            "orderable":      false,
            "data":           null,
            "defaultContent": '<button>Infos</button>'
            },
        {"data": "Matricula"},
        {"data": "DataMatricula"},
        {"data": "Modelo"},
        {"data": "Motorização"},
        {"data": "Carroçaria"},
        {"data": "Equipamento"},
        {"data": "Combustivel"},
        {"data": "Cor"},
        {"data": "Kilometros"},
        {"data": "PreçoRecomendado"},
        {"data": "Caixa"},
        {"data": "TipoViatura"},
        {"data": "Extras"}

    ],
    "order": [[1, 'asc']],


    //initComplete: function() {
      //  buildSelect(table);
        //table.on('draw', function() {
          //  buildSelect(table);
        //});   
        initComplete: function () {
        count = 0;
        this.api().columns().every( function () {
            var title = this.header();
            //replace spaces with dashes
            title = $(title).html().replace(/[\W]/g, '-');          


          //use column title as selector and placeholder
          $('#' + title).select2({
            multiple: true,
            closeOnSelect: false,
            placeholder: "Select a " + title
          });

          //initially clear select otherwise first option is selected
          $('.select2').val(null).trigger('change');
        } );        


    $('#example tbody').on( 'click', 'button', function () {
        var data = table.row( $(this).parents('tr') ).data();
        alert( data[0] +"'s salary is: "+ data[ "Cor" ] );
    } );    

    $('#example tbody').on('click', 'td.details-control', function () {
    var tr = $(this).closest('tr');
    var row = table.row( tr );

    if ( row.child.isShown() ) {
        row.child.hide();
        tr.removeClass('shown');
    }
    else {
        row.child( format(row.data()) ).show();
        tr.addClass('shown');
    }
    } );
    }
});

});`

kthorngren

Can you put this together in a test case so we can see what is happening? Scanning a bunch of code is difficult to do. All you need to do is provide the essential code to show the issue.

Kevin

nelsondcosta

Well mate, i cant put it work with JS BIn (im new)
http://live.datatables.net/wahowodu/2/

In my xampp

If might help you I can share my code.

nelsondcosta

Hi mates, I just edited inside JS bin and now works +/-
http://live.datatables.net/kefojuse/3/edit

Can help me out how to use select 2?
For now I can only select 1 option, need 2 or more.

nelsondcosta

The select 2 option its link this -> http://live.datatables.net/xehexoye/1/edit

kthorngren

Here is your updated example with select2:
http://live.datatables.net/nebujaya/1/edit

Changes made:
1. Added the select2.js and css libraries.
2. Replaced the code with the select2 code from the example.
3. Changed the appendTo() selector to match what you have in your regular select example.
4. Commented you this code in initComplete:

            table.on('draw', function() {
                //buildSelect(table);
            });     

Commented out the code to update the select options because it causes a stack overflow error. A different solution will need to be created to handle updating the select options using select2. Here is the select2 docs for programmatic control of select2:
https://select2.org/programmatic-control/add-select-clear-items

I don't have an example of updating the select2 options. Someone on the forum may but you might need to look on Stack Overflow and the select2 docs to learn how to update the select2 options. If I have time later I'll look but no promises.

I also noticed that you have two sets of sorting icons. This is due to conflicting CSS files. I commented out the jquery.dataTables.min.css include to fix your styling issues.

Kevin

nelsondcosta

Hi kthorngren, thanks alot it work

Just a question plz. There is any way to when I pick "London and Sidney"
The others filters"pre-filter" the option available for "London and Sidney"???

Once again thanks

kthorngren

The others filters"pre-filter" the option available for "London and Sidney"???

That is what I mentioned above. The docs for select2 will need to be reviewed to learn how to do this.

Kevin