How to filter out rows with null values

mihalisp

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.

kthorngren

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

mihalisp

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();
    }

mihalisp

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.

colin

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

mihalisp

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 )) ) ?

mihalisp

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 '';}

}