Server Side search with multiple strings across multiple columns

deetimba

Hey there,

what seems to work by default for client side implementation is not working for the server side tables: I would like to be able to search for: "London Black Engineer" returning me a list of people living in London (column city), named Black (column name) and being engineers (column job).

So far I have not been able to find a appropriate approach to it. Also said that I'm quite new to data tables.

How could I manage to have this enabled? Any ideas?

rf1234

It is a pretty old question and you've probably found a solution for it. But since I am dealing with similar issues here is a solution I've just finished testing. I defined a field called "global_search_field" that contains a concatenation of a couple of fields. Now I can enter stuff like "LIBOR USD 3months" which is ref_rate plus currency plus ref_rate_period and it is being found. (But: "USD 3months LIBOR" isn't found with this unfortunately because Editor is obviously applying the complete search string to every field as a LIKE statement. If EDITOR were splitting the string into individual words and searched for LIKE '%USD%' OR LIKE '%3months%' OR LIKE '%LIBOR%' it would work but unfortunately it doesn't.

The global_search_field must be defined in Javascript as well even though I don't return it to the front-end (getFormatter returns space) and I guess also in the HTML. I use class "never" so that it isn't displayed and also make sure it doesn't show up in the colvis selection either.

@allan I tried something more complex for the concatenated field like this:

select  
CONCAT( rate.ref_rate, " ", 
        rate.currency, " ", 
        rate.ref_rate_period, " ", 
        DATE_FORMAT(rate.date, "%d.%m.%Y") ) 
AS global_search_field 
from rate

this works fine with MySQL but it doesn't work with Editor PHP. Editor doesn't seem to be able to handle DATE_FORMAT inside CONCAT. Is there a solution for this? And also are you planning to enhance server side search by using more differentiated LIKE statements in Editor's SQL?

HTML:

<div class="container"> 
    <table id="tblRate" class="table table-striped table-bordered rateTables hidden"
           cellspacing="0" width="100%">
        <thead>
            <tr>
                <th>Ref Rate</th>
                <th>Currency</th>
                <th>Period</th>
                <th>Date</th>
                <th>Rate</th>
                <th>Timestamp</th>
                <th class="never">SearchField</th>
            </tr>
        </thead>
    </table>
</div>

Javascript:

var rateTable = $('#tblRate').DataTable( {
    dom: "Bfrltip",
    processing: true,
    serverSide: true,    //server side only works well with type "POST" !!!
    ajax: {
        url: 'actions.php?action=tblRate',
        type: 'POST'
    },
    pageLength: 20,
    lengthMenu: [5, 10, 20, 50, 100, 200, 500],
    columns: [
        {   data: "rate.ref_rate" },
        {   data: "rate.currency" },
        {   data: "rate.ref_rate_period" },
        {   data: "rate.date" },
        {   data: "rate.rate" },
        {   data: "rate.update_time" },
        {   data: "global_search_field" }
    ],
    order: [[ 3, 'desc' ]],
    select: {
        style: 'multi'
    },            
    buttons: [
        {   extend: "create", editor: rateEditor },
        {   extend: "edit",   editor: rateEditor },
        {   extend: "remove", editor: rateEditor, name: "remove" },
        {   extend: "colvis", columns: ':not(.never)' },
    ]
} );

PHP:

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' ),
        Field::inst( 'CONCAT(rate.ref_rate, " ", rate.currency, " ", rate.ref_rate_period) AS global_search_field' )->set( false )
            ->getFormatter( function ( $val, $data, $opts ) {
                return '';                     
            } )
    )
    ->process($_POST)
    ->debug( true )
    ->json();

rf1234

of course it must be "LIKE '%USD%' AND LIKE '%3months%' AND LIKE '%LIBOR%'" in my note above.