The search does not filter the manipulated text

info@abakon.it

Hello,
i am using server side function. I modified data_output so that instead of the customer_code in the table, the customer's name and surname come out. I get the visual result correctly, but if I filter by the customer name it doesn't find it, while if I filter by the customer_code the filter works. How can I fix?

allan

I don't know what data_output or customer_code are here. Could you link to a test case showing the issue so we can help to debug it please.

Allan

info@abakon.it

I got the datatable script on github

https://github.com/DataTables/DataTables/blob/master/examples/server_side/scripts/ssp.class.php

I used the complex function to fetch the raw results from the db. Among these, one of the results is the customer code. Instead of the customer code, I display the customer's Name and Surname by manipulating the result from data_output as seen in the script below.

class SSP {
/**
* Create the data output array for the DataTables rows
*
* @param array $columns Column information array
* @param array $data Data from the SQL get
* @return array Formatted data in a row based format
*/
static function data_output ( $columns, $data )
{
global $link;
$out = array();

    for ( $i=0, $ien=count($data) ; $i<$ien ; $i++ ) {
        $row = array();

        for ( $j=0, $jen=count($columns) ; $j<$jen ; $j++ ) {
            $column = $columns[$j];

            // Is there a formatter?
            if ( isset( $column['formatter'] ) ) {
                $row[ $column['dt'] ] = $column['formatter']( $data[$i][ $column['db'] ], $data[$i] );
            }
            else {
                $row[ $column['dt'] ] = $data[$i][ $columns[$j]['db'] ];
                if ($column['db']=='nomecognome') {
                    $row[ $column['dt'] ]= detect_arg('nomecognome', $row[$column['dt']]);
                }
            }
        }

        $out[] = $row;
    }

    return $out;
}

...........

The problem is that the customer's first and last name are displayed correctly but the filter does not work. Instead, if I filter by customer code, the filter works

example result:

LOREM IPSUM (1234567)

It seems that the filter only works on the result of the select on the db and not on the visual result present in the table

info@abakon.it

allan

I would suggest you log the SQL statement that the modified SSP class is generating and check that it is applying the WHERE condition correctly.

Allan

info@abakon.it

I'm sorry, maybe I wasn't clear. As you can see in the table, if in "Filter the results" I insert Marck which is a data modified in data_output the filter does not work, while if I type 32641241 which is the data taken from the database the filter works.

allan

Oh I see - thanks for the clarification. The formatted data cannot be searched, since the search is performed at the database level. The "Marck" data would have to be in a database column in order for the SQL statement to be able to search it.

Allan

allan

Do you need server-side processing for this table? Have you got tens of thousands of rows?

Allan

info@abakon.it

Yes, I have the mandatory need to use server-side processing. But shouldn't Datatable read the result after the ajax call? What could be an alternative? Among other things, after viewing the result, I would need to send an input post that should modify the result, Datatable tells me that I can't redeclare it. But let's go step by step maybe it's better to open another discussion.

kthorngren

I'm not familiar with PHP have not used the ssp.class.php script but it looks like the filter function would need some refactoring. I think some logic would need to be added in if ( isset($request['search']) && $request['search']['value'] != '' ) { to determine if an additional where clause should be added for the name column to search for $request['search']['value'].

The client side rendering is not communicated to the server script. Although you could add this capability by using ajax.data as a function and sending the appropriate mappings to use server side. Similar to this example.

Kevin

allan

Doing the "processing" aspect in the PHP (rather than SQL) would mean that you need to have the entire data set available in PHP. That's where you are looking to apply the filter, so that's where the full data set would need to be. It is possible to do that, but given that SQL engines are specifically tuned for this sort of data processing (paging, search and ordering) it makes sense to use them. I think you'd basically need to start again with the SSP class - it is entirely dependent on SQL.

Can you do your "rendering" of the data in SQL? That would then allow the filter to be applied to it. You could use a VIEW for example for more complex conditions.

Allan