server side search with getformatter

HassanDomeDenea

Greetings
Well I'm using serverside processing to get my rows.
In server, I use getFormatters for changing some values.
For example in database gender column, gender stored as 1 and 0. With getFormatter I change 1 to male and 0 for female and view them like this.
Everything Ok for now.
The problem when I try to use the searchbox filter generated by datatable. When I type male, no rows rendered. I realized the search query run against datatable fields before getting formatted. (I have to search for 1 to get males rows)
Any solution or option to fix this ?

HassanDomeDenea

Note: Same thing with sorting when clicking on column header, it is based on original database values, not the rendered/formatted ones.

colin

Hi @HassanDomeDenea ,

If you're using serverSide, your server script will need to be aware of that change and apply the search accordingly. If you'e not using serverSide, it should work, since the client is only aware of male and female.

If that doesn't work, could you link to your page please. 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

HassanDomeDenea

@colin
Thanks for reply.
I'm using it currently on localhost.
But here are the files:

HTML:

<script src="js/jquery.js"></script>

    <link href="sources/semantic.min.css" rel="stylesheet">
    <script src="sources/semantic.min.js"></script>

    <script src="sources/datatables.min.css"></script>
    <script src="sources/datatables.min.js"></script>

    <table id="test" class="ui table hover stripped">
      <thead>
        <th>Name</th>
        <th>Gender</th>
        <th>Addess</th>
      </thead>
    </table>

and JS:

$('#test').DataTable({
        processing:true,
        serverSide:true,
        ajax:{url:'testDBServer.php',method:'post'},
        columns: [
          {data: 'name'},
          {data: 'gender'},
          {data: 'address'}
        ]
      })

and PHP

include("conf/editordb/lib/DataTables.php");

$db = new DataTables\Database(array(
    "type" => "Sqlite",     // Database type: "Mysql", "Postgres", "Sqlserver", "Sqlite" or "Oracle"
    "user" => "",          // Database user name
    "pass" => "",          // Database password
    "host" => "", // Database host
    "port" => "",          // Database connection port (can be left empty for default)
    "db" => "test.db",          // Database name
    "dsn" => "",          // PHP DSN extra information. Set as `charset=utf8mb4` if you are using MySQL
    "pdoAttr" => array()   // PHP PDO attributes array. See the PHP documentation for all options
));

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

Editor::inst($db, 'test', 'id')
        ->where('key', '0000')
        ->fields(
            Field::inst('id'),
            Field::inst('name'),          
            Field::inst('gender')->getFormatter(function ($val, $data) {
                if ($val == '1'){
                  return 'Male';
                }else{
                  return 'Female';
                }                
            }),
            Field::inst('address')->getFormatter(function ($val, $data) {
                if ($val == ''){
                  return 'No location';
                }else{
                  return $val;
                }
            })
    )
        ->process($_POST)
        ->json();
  

and this is the Database

Here is the result:

Now when I type in search "Male", results is empty. When I type "1" in the search box, results will show males person.

Same as sort, when I filter gender column, and let suppose female are coded as (2) and males as (1), the results will show male before females, because 1 before 2, not the formatted data (F before M).

allan

Hi,

The issue here is that the search is happening in SQL, but the formatting is happening in PHP - i.e. after the search has already happened. So unfortunately there is no way to use a getFormatter's result as part of the search term.

What you need to do instead is create an SQL VIEW which will use SQL conditional functions to do the formatting. Then you can query the view and that will be searchable by the formatted term.

Allan

HassanDomeDenea

@allan
Ok that's clear now.
If this feature can be added (i.e. option to make search run after formatting, not in the SQL), that would be cool.
(BTW same thing for sorting, it is based on original data from SQL, not on the php formatted fields).