How can we search a null value with serverside (table.columns(12).search('^$', true, false)) ?

XavierO

Hi,

I tried and searched to search a null value with a serverside.

In my JSON (return by the php file form the editor), my field is like this {"FIELD": null}.
I saw in the documentation that I need this line to search in a column :
table.columns(12).search('^$', true, false).draw();

However, it doesn't work (normally '^$' to found an empty or null value. I tried too '^\s*$'). I can just search a string (ex : 'TOTO' or '^TOTO$' and here, it's working).

I think that we can search only on string value ... Can you confirm that or, if we can search a null value, how can we do it ?

Form what I saw, we can't search a null value, but topics was a little old. I hope that since, a release already add this feature

Thanks
BR
Xavier

kthorngren

table.columns(12).search('^$', true, false).draw(); works here with either null or "":
http://live.datatables.net/horofato/1/edit

You can change the null to "" and the search will still work. Please provide or update my example to replicates the issue.

Kevin

kthorngren

Sorry, just noticed in your title you mention "with serverside". If you have serverSide: true then your server script is responsible for handling the search. Are you using SSP script provided by Datatables or your own?

Kevin

XavierO

Hi Kevin,

Thanks for your response.
In fact, I use the serverSide. I have so much data, no choice

I use the php file generate by DataTables Editor with the generator.

Exemple without all fields :
Editor::inst( $db, 'view_pa', 'pa_code' )
->fields(
Field::inst( 'view_pa.pa_code' ),
Field::inst( 'view_pa.id_view_pa' ),
Field::inst( 'view_pa.id_ref_city' ),
Field::inst( 'view_pa.pm_code' ),
Field::inst( 'view_pa.pm_state' ),
Field::inst( 'view_pa.pm_mesc_arcep_date' )
->validator( Validate::dateFormat( 'Y-m-d' ) )
->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
->setFormatter( Format::dateFormatToSql( 'Y-m-d' ) ),
Field::inst( 'view_pa.pa_state' ),
Field::inst( 'view_pa.visit_network_date' )
->validator( Validate::dateFormat( 'Y-m-d' ) )
->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
->setFormatter( Format::dateFormatToSql( 'Y-m-d' ) ),
Field::inst( 'city.name' ),
)
->leftJoin( 'ref_city', 'ref_city.id_ref_city', '=', 'view_pa.id_ref_city' )
->leftJoin( 'city', 'city.id', '=', 'ref_city.id_city' )
->process( $_POST )
->json();

For exemple, I need to search null value for "view_pa.pm_mesc_arcep_date". In my JSON response I have {"view_pa.pm_mesc_arcep_date": null}

Thanks for your help
Xavier

allan

Hi Xavier,

If its a global filter you want (i.e. not something the user is typing in) then you could use:

->where('view_pa.pm_mesc_arcep_date', null)

immediately before the ->process() call.

Does that do what you need?

Allan

XavierO

Hi Allan,

Thanks.

In fact, when I load my page, I want see all my data (null value too).
And, for example, when I click on a button, I want see only the row where I have a null value on a field (like view_pa.pm_mesc_arcep_date).

If I put the line "->where('view_pa.pm_mesc_arcep_date', null)", I'll not all the data when I load my page.

To resume : I want apply a filter on null value when I click on a button else, I want see all my data.
I don't know if I'm clear ^^

Thanks
Xavier

allan

Ah - so you want to conditionally add that conditional filter? .

For that do something like:

$editor - Editor::inst( $db, 'view_pa', 'pa_code' )
->fields(
   ...
)
->leftJoin(...);

if (isset($_GET['nullOnly'])) {
  $editor->where('view_pa.pm_mesc_arcep_date', null);
}

$editor
->process( $_POST )
->json();

And send a nullOnly property to the server with each DataTable's Ajax request, which you can do through the ajax.data option (as a function in this case).

Allan

XavierO

Hi allan,

Sorry for the delay, I was in holiday ^^
It seems to match what I'm looking for. I have not thought of this possibility. I test and confirm this!

Thanks
Xavier

XavierO

It's working !

Thanks for your help !

Xavier