Null values cannot be filtered

Null values cannot be filtered

lancwplancwp Posts: 85Questions: 18Answers: 1
  •     this.api().columns([2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33]).every( function () {             
                    var column = this;
                    var select = $('<select style="border:1px solid #ced4da;height:30px"><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 ) {
                     if(column.search() === '^'+d+'$'){
            select.append( '<option value="'+d+'" selected="selected">'+d+'</option>' )
        } else {
            select.append( '<option value="'+d+'">'+d+'</option>' )
        }
                    } );
                } );
            },**: 
    

There are null values and null values in my data. Other values can be filtered by drop-down, but null values and null values cannot be filtered. Is there a problem with my local code? Please help correct it.

This question has an accepted answers - jump to answer

Answers

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

    Are the null values empty strings? If so you will need to create a search plugin to filter out empty cells. When using search() or column().search() with en empty string, ie "", Datatables will use this to reset the search and display all rows. See this thread for an example of a search plugin with empty strings.

    If this doesn't help please provide a test case with an example of your null data so we can understand exactly what you have to offer suggestions.
    https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

    Kevin

  • lancwplancwp Posts: 85Questions: 18Answers: 1
    $('#example').on( 'click', 'tbody td:not(:first-child)', function (e) {
            editor.inline( this, {
                onBlur: 'submit',
            submit: 'allIfChanged'
            } );
        } );
    
        $('#example').DataTable( {
    
    //求和
    
     "footerCallback": function ( row, data, start, end, display ) {
                var api = this.api(), data;
    
                // Remove the formatting to get integer data for summation
                var intVal = function ( i ) {
                    return typeof i === 'string' ?
                        i.replace(/[\$,]/g, '')*1 :
                        typeof i === 'number' ?
                            i : 0;
                };
    
       // Total over all pages
                total = api
                    .column( 13 ,{ search: 'applied' })
                    .data()
                    .reduce( function (a, b) {
                        return intVal(a) + intVal(b);
                    }, 0 );
    
                // Total over all pages2
                total2 = api
                    .column( 11,{ search: 'applied' } )
                    .data()
                    .reduce( function (a, b) {
                        return intVal(a) + intVal(b);
                    }, 0 );
    
                 // Total over all pages2
                total3 = api
                    .column( 12,{ search: 'applied' } )
                    .data()
                    .reduce( function (a, b) {
                        return intVal(a) + intVal(b);
                    }, 0 ); 
    
    
                // Total over this page
                pageTotal = api
                    .column( 13, { page: 'current'} )
                    .data()
                    .reduce( function (a, b) {
                        return intVal(a) + intVal(b);
                    }, 0 );
    
    
    
                // Update footer
            $("#example tfoot tr:eq(0) th:eq(1) ").html(
                    Math.round(total2*100)/100
                );  
    
              $("#example tfoot tr:eq(0) th:eq(2) ").html(
                    Math.round(total3*100)/100
                );
                 $("#example tfoot tr:eq(0) th:eq(3) ").html(
                   Math.round(total*100)/100
    
                );
    
    
    
    
    
            },
    
    
    
            "sProcessing": "正在加载中...", 
    //   "bStateSave": true,//是否启用服务器处理数据源,必须sAjaxSource指明数据源位置
            "bProcessing": true, 
            "bDeferRender": true, //是否启用延迟加载:当你使用AJAX数据源时,可以提升速度。  
    //      "bStateSave": true, //是否开启cookies保存当前状态信息
    
               "initComplete":function () {
                // Apply the search
    
    //        this.api().columns().every( function () {
            this.api().columns([2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33]).every( function () {             
                    var column = this;
                    var select = $('<select style="border:1px solid #ced4da;height:30px"><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 ) {
                     if(column.search() === '^'+d+'$'){
            select.append( '<option value="'+d+'" selected="selected">'+d+'</option>' )
        } else {
            select.append( '<option value="'+d+'">'+d+'</option>' )
        }
                    } );
                } );
            },
    
            dom: "Bfrtip",
            ajax: "controllers/staff.php",
    
            columns: [
                {
                    data: null,
                    defaultContent: '',
                    className: 'select-checkbox',
                    orderable: false
                },  
                { data: "id" },     
                { data: "ydh" },
                { data: "hppic",
                    render: function ( file_id ) {
                        return file_id ?
                            '<img src="'+editor.file( 'files', file_id ).web_path+'" width="20px"/>' :
                            null;
                    },
                    defaultContent: "无图片",
                    title: "图片"
                },
                { data: "statu" },
                { data: "hh" },
                { data: "customer" },
                { data: "brand" },
                { data: "business" },
                { data: "nature" },
                { data: "size" },
                { data: "color" },
                { data: "pairs" },
                { data: "sumpairs" },
                { data: "xdtime" },
                { data: "wctime" },
                { data: "gq" },
                { data: "xqk" },
    
                { data: "jl" },
                { data: "bs" },
                { data: "zb" },
                { data: "jytime" },
                { data: "bll" },
                { data: "bql" },
                { data: "fltime" },
    
                { data: "cjl" },
                { data: "cjql" },
                { data: "gp" },
    
                { data: "zcl" },
                { data: "zcql" },
                { data: "zc" },
    
                { data: "dd" },
                { data: "cx" },
                { data: "dzt" }
    
    
    
    
            ],
            "oLanguage": {
    "sLengthMenu": "每页显示 _MENU_ 条记录",
    "sSearch":"全文搜索:",
    "sZeroRecords": "抱歉, 没有找到",
    "sInfo": "从 _START_ 到 _END_ /共 _TOTAL_ 条数据",
    "sInfoEmpty": "没有数据",
    "sInfoFiltered": "(从 _MAX_ 条数据中检索)",
    "oPaginate": {
    "sFirst": "首页",
    "sPrevious": "前一页",
    "sNext": "后一页",
    "sLast": "尾页",
    
    },
    "sZeroRecords": "没有检索到数据",
    "sProcessing": "<img src='assets/images/loaders/loader_gr.gif' />"
    },
    "aLengthMenu": [15, 50,100],
    "iDisplayLength": 15,  
            order: [1,'desc' ],
            select: {
                style:    'os',
                selector: 'td:first-child'
            },
          select: true,
            colReorder: true,
            buttons: [
                {
                    extend: "create",
                    editor: editor,
                    formButtons: [
                        '新建',
                        { text: '取消', action: function () { this.close(); } }
                    ]
                },
                {
                    extend: "edit",
                    editor: editor,
                    formButtons: [
                        '修改',
                        { text: '取消', action: function () { this.close(); } }
                    ]
                },
                {
                    extend: "selected",             
                    text: '删除',
                    action: function ( e, dt, node, config ) {
                        var rows = table.rows( {selected: true} ).indexes();
    
                        editor
                            .hide( editor.fields() )
                            .one( 'close', function () {
                                setTimeout( function () { // Wait for animation
                                    editor.show( editor.fields() );
                                }, 500 );
                            } )
                            .edit( rows, {
                                title: '删除',
                                message: rows.length === 1 ?
                                    '你确认要删除这行吗?' :
                                    '你确认要删除这'+rows.length+' 行?',
                                buttons: '删除'
                            } )
                            .val( 'removed_date', (new Date()).toISOString().split('T')[0] );
                             }
                    },
                 {
                    extend: 'collection',               
                    text: '导出',
                    buttons: [
                        'copy',
                        'excel'
    
                    ]
                }
            ]
        } );
    

    Thank you help,There are two kinds of null values in the database, one is no value, and the other is null,I use the drop-down selection of all fields to filter,There are many fields with null values, such as JL fields

  • lancwplancwp Posts: 85Questions: 18Answers: 1

    When the current selection is null, it cannot be filtered

  • allanallan Posts: 61,438Questions: 1Answers: 10,052 Site admin
    Answer ✓

    The search() and column().search() methods always use string based matching. They are not type aware so null itself can't be searched for using these methods. Also, for these two methods an empty string is the same as "no filter".

    If you need type aware filtering, you would need to create a search plug-in that would handle null values as well as empty strings and any other value you pass in.

    Regards,
    Allan

Sign In or Register to comment.