How to filter out rows with null values

How to filter out rows with null values

mihalispmihalisp Posts: 127Questions: 22Answers: 0

Hi,

i am trying to use a checkbox to filter a Datatable column
When the checkbox is checked i want the Datatable to show only non null-non empty rows.

I have searched the forum,i found similar posts talking about the opposite (eg. show only nulls ) but didn't find a way to achieve this.

For my other filters i have used:

  $('input:checkbox[name="chk_box"]').on('change', function () {
  //build a regex filter string with an or(|) condition
  var filter = $('input:checkbox[name="chk_box"]:checked').map(function() {
  return this.value; 
  }).get().join('|');

  table.column(17).search(filter ? '^((?!value1|value2).*)$' : '', true, false, false).draw(false);

I have also deen these about filtering null values.how can i revert it to achieve the desired result?

   //table_elleimma_iperwria.column(17).search( '^$', true, false ).draw();
   //table_elleimma_iperwria.column(17).search( '' ).draw();

Thanks.

Answers

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

    I think I would create a Search Plugin for this. Here is an example:
    http://live.datatables.net/fehobiti/1/edit

    Using the search plugin has the advantage that it will run anytime the table is searched, sort, etc - anytime there is a table draw.

    Kevin

  • mihalispmihalisp Posts: 127Questions: 22Answers: 0

    Thank you Kevin!

    I have also found another working solution for anyone interested!
    Can you check it please?

     $('input:checkbox[name="chk_box"]').on('change', function () {
    
     if ($(this).is(':checked')) { 
                     table
                    .column(17) // or columns???
                    .search( '^(?!\s*$).+', true, false )
                    .draw();
    
        }
        else {
                     table
                    .column(17)
                    .search( '' ) 
                    .draw();
        }
    
  • mihalispmihalisp Posts: 127Questions: 22Answers: 0

    Here again.

    Well,i am trying to change your example a little bit , kthorngren .
    i don't want to exclude null rows ,but the opposite i want to show only rows with null data(dates,datetime from db) in either column 7 or 8 .
    So i use the following

       if (checked && (  ((searchData[7] != null ) && (searchData[8] != null )) ||  ((searchData[7] == null ) && (searchData[8] == null )))   ) /
           {
             console.log( (searchData[7]) ) ;// for Null dates it returns 1970/01/01 02:00:00
             console.log( (typeof(searchData[7])) ) ; //it returns string
    
             return false; 
            }
      else  
           {return true;}
    

    It works only if i change searchData[7] == null to searchData[7] == '1970/01/01 02:00:00'

    The SQL code for column 7 (and 8) is CONVERT(varchar,field_column_7,120)

    My render function for columns 7 and 8 is:

      "render": function(data, type, full) {
              if (type == 'display') {
                   if (data != null) {return moment(new Date(data)).locale('el').format('HH:mm');}
                  else {return '';} 
                  }
              else
                    {return moment(new Date(data)).format('YYYY/MM/DD HH:mm:ss'); } 
    

    What am i missing here?
    Thank you again.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @mihalisp ,

    I would've expected this to work:

    if (checked && ((searchData[7] == null ) || (searchData[8] == null ))  ) 
    
    

    If no joy, we're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

    Cheers,

    Colin

  • mihalispmihalisp Posts: 127Questions: 22Answers: 0

    I don't think i need to create a test case.
    I mean it has to do with the way null dates appear-exist in my Datatable.

    console.log shows null dates as a string (1970/01/01 02:00:00).On the Datatable i see them as ' '.

    Does if (type == 'display') need to be changed in this situation?
    What is the meaning of if (type == 'display') ?
    It is not applied at: if (checked && ((searchData[7] == null ) || (searchData[8] == null )) ) ?

  • mihalispmihalisp Posts: 127Questions: 22Answers: 0

    I resolved it.
    I changed the render to:

        "render": function(data, type, full) {
            if (type == 'display') {
               if (data != null) {return moment(new Date(data)).format('HH:mm');}
                else {return '';} 
              }
            else
           if (data != null) {return moment(new Date(data)).format('YYYY/MM/DD HH:mm:ss'); } 
           else {return '';}
    

    }

This discussion has been closed.