Custom (browser) rendered columns on serverside Searchpanes implementation

marabou

Hi everyone,

I have a rather large datatable (& editor) in terms of both columns and dataset. It has been growing increasingly slow as the rows go up.

I use the searchpanes for filtering the columns, some of which are directly from the database, whilst others are custom rendered on the browser. (ex. concatenation or maths)

I tried converting to serverside processing (big improvement) however the searchpane function for my custom rendered columns is lost (no data).

Does anyone have an idea on how to overcome this?

Thanks a lot

kthorngren

See if this blog helps.

Kevin

rf1234

If you change your settings to serverside you have to accept that the search function is performed on the server based on the raw data BEFORE getFormatting. That is very different from what you are used to!

To make search work again you can either manipulate the data entered by the user to match the format on the server or build views that emulate end user rendering on the server. These views could then be searched by Editor.

Either way: It is a lot of work. I posted a couple of things on that some time ago in this forum.

This is on manipulating the search data client side:
https://datatables.net/forums/discussion/comment/125435/#Comment_125435

This is a use case to search money market rates from a server side data table. Two languages are supported, English and German. The number and the date formats deviate from each other in English and German - and mostly they also deviate from what is saved in the data base.

This is the Editor instance server side (PHP):

if ($lang === 'de') {     
    $searchTable = "rate_search_german";
} else {
    $searchTable = "rate_search_english";
}
Editor::inst( $db, 'rate' )
    ->field(
        Field::inst( 'rate.ref_rate' ),
        Field::inst( 'rate.currency' ),
        Field::inst( 'rate.ref_rate_period' ),
        Field::inst( 'rate.date' )
            ->getFormatter( function ( $val, $data, $opts ) {
                    return getFormatterDate($val);                     
                } )
            ->setFormatter( function ( $val, $data, $opts ) {
                    return setFormatterDate($val);
                } ),
        Field::inst( 'rate.rate' )
            ->validator( function ( $val, $data, $opts ) {
                return validatorRate($data['rate'], $val, true);
            } )
            ->getFormatter( function($val, $data, $opts) {
                return getFormatterRate($val);
            })
            ->setFormatter( function($val, $data, $opts) {
                return setFormatterRate($val);
            }),
        Field::inst( 'rate.update_time' )
    )
    ->leftJoin( $searchTable,  'rate.id', '=', $searchTable . '.rate_id')
//        ->leftJoin( 'rate_search_german',  'rate.id', '=', 'rate_search_german.rate_id')
//        ->leftJoin( 'rate_search_english', 'rate.id', '=', 'rate_search_english.rate_id')
    ->where( function ( $q ) {
        if ( isset($_SESSION['infoPage']) ) {
            $q  ->where( 'rate.date', '2009-12-31', '>' );
        }
        if ( isset($_POST['fullTextSearchString']) ) {
            if ( $_POST['fullTextSearchString'] > '' ) {
                if ($_SESSION['lang'] === 'de') {   
                    $q  ->where( 'rate_search_german.global_search_field', '%'. trim($_POST['fullTextSearchString'], '"') .'%', 'LIKE' );
                } else {
                    $q  ->where( 'rate_search_english.global_search_field', '%'. trim($_POST['fullTextSearchString'], '"') .'%', 'LIKE' );
                }
            }
        }
    } )              
    ->process($_POST)
    ->json();

These are the definitions of the two views I use for the search: rate_search_german and rate_search_english

CREATE VIEW `rate_search_german` AS
SELECT id                               AS rate_id,
       CONVERT(
       CONCAT_WS ( SPACE(1), 
       currency,
       ref_rate,
       ref_rate_period,
       DATE_FORMAT(date, '%d.%m.%Y'),
       REPLACE(rate, '.', ','),
       update_time ) 
       USING utf8mb4                ) 
       COLLATE utf8mb4_general_ci   
                                        AS global_search_field
FROM rate

CREATE VIEW `rate_search_english` AS
SELECT id                               AS rate_id,
       CONVERT(
       CONCAT_WS ( SPACE(1), 
       currency,
       ref_rate,
       ref_rate_period,
       DATE_FORMAT(date, '%d/%m/%Y'),
       rate, 
       update_time ) 
       USING utf8mb4                ) 
       COLLATE utf8mb4_general_ci   
                                        AS global_search_field
FROM rate

And finally the client side data table:

var rateTable = $('#tblRate').DataTable( {
    dom: "Bfrltip",
    processing: true,
    serverSide: true, 
    stateSave: true,
    ajax: {
        url: 'actions.php?action=tblRate',
        type: 'POST',
        data: function ( d ) {            
            d.fullTextSearchString = d.search.value;
        }
    },
    language: {
        "searchPlaceholder": lang === 'de' ? 
                    "z.B. usd libor overnight 17.06.2019" :
                    "e.g. usd libor overnight 17/06/2019",
        "search": lang === 'de' ? 
                    "Suche (Geduld bitte: 100k+ Datensätze!):" :
                    "Search (Patience please: 100k+ records!):"
    },
    pageLength: 20,
    lengthMenu: [5, 10, 20, 50, 100, 200, 500],
    columns: [
        {   data: "rate.currency" },
        {   data: "rate.ref_rate" },
        {   data: "rate.ref_rate_period" },
        {   data: "rate.date" },
        {   data: "rate.rate" },
        {   data: "rate.update_time" }
    ],
    columnDefs: [
        // targets may be classes
        {targets: [0, 1, 2, 3, 4, 5], searchable: false}
    ],
    order: [[ 3, 'desc' ]],
    select: {
        style: 'single'
    },            
    buttons: [
        {   extend: "create", editor: rateEditor, className: "lgfAdminOnly" },
        {   extend: "edit",   editor: rateEditor, className: "lgfAdminOnly" },
        {   extend: "remove", editor: rateEditor, className: "lgfAdminOnly" },
        {   extend: "colvis", columns: ':not(.never)' }
    ]
} );

I turned off search in the individual columns in columnDefs as well.

As you can see: this is highly individual. I would only use it if there is no other way ... You might as well have to limit user search.

marabou

Thanks everyone for the suggestions!

@rf1234, I agree that views are a labour intensive but straightforward solution. Nice code!

Best regards,

N