Built-In DT filtering on Server Side Processing - word by word on all columns

trevoc

Been filing over all the forum threads to find a solution before posting.

I'm aware from the comment in the ssp.class.php file (see below) that the standard or default filtering (word by word on all columns) works different than the server side processing filtering implemented in the ssp file (Single Column).

Comment in ssp.class.php:

     * Searching / Filtering
     *
     * Construct the WHERE clause for server-side processing SQL query.
     *
     * NOTE this does not match the built-in DataTables filtering which does it
     * word by word on any field. It's possible to do here performance on large
     * databases would be very poor

I'm curious, it mentions above that "It's possible to do here performance..."

Does this mean it's possible to do by setting an option in the DataTables initialization JavaScript?, or by easily editing the filter function, or is this simply stating that it's possible to do if you re-write the below code.

I would like to see the performance difference as I have tables that are too large for rendering from the DOM on load, but are not necessarily huge (500-1500 records).

Wondering if anyone can point me in the right direction. Would like to be able to search for FIRST_NAME LAST_NAME in search field for instance. On Server-side processing as soon as you put in a space, it returns no records as the first and last name are stored in separate columns.

TIA, any help is very much appreciated.

trevoc

For those stumbling into this discussion, I've found the following threads about this, although no solution so far, It does provide a lot of insight.

https://datatables.net/forums/discussion/22076/global-search-not-functioning-correctly

https://datatables.net/forums/discussion/3343/server-side-processing-and-regex-search-filter/p1

Also this, not completely relevant, but some interesting information for those that are already editing the ssp.class for extra where conditions.... I just hard coded my extra where conditions to limit the records into the simple function, but just noticed the complex version at the end. Looks like you can pass extra where conditions to the complex function, or read some of the edits this guy did.

https://emranulhadi.wordpress.com/2014/06/05/join-and-extra-condition-support-at-datatables-library-ssp-class/

Should have dived deeper into this class earlier, but I thought there might be a quick option to set etc...

Will post my findings.

Also... any fingers pointing to the right direction will appreciated and manicured.

TIA.

trevoc

More than one way to skin a cat.

OK... by no ways is this a solution to my above question, but it does produce a desirable result for simple searches that could not be performed before.

Lets say these are my columns before solution with some data retrieved by:
SELECT firstname, lastname, telephone, email FROM...

FirstName | LastName | Telephone | Email
John | Smith | 555-1234 | john@email.com

If I search for john it shows record (plus all other instances of john in all columns)

If I search for Smith it shows the same (plus all other instances of smith in all columns)

If I search for "John Smith" no records are returned as no exact match can be found.

If I change my sql query to:
SELECT CONCAT_WS(' ', firstname, lastname) AS fullname, firstname, lastname, email, telephone FROM....

I get an extra column of data that displays:

FullName | FirstName | LastName | Telephone | Email
John Smith | John | Smith | 555-1234 | john@email.com

Now when I search for "John Smith" it returns the proper record.

I can hide this fullname column from being displayed but search still enabled.

I can also create as many extra "search" columns that I think might need to be matched and hide them also.

In order to inject my own SQL after select statement, I edit the following code in the ssp.class.php file from:

        // Main query to actually get the data
        $data = self::sql_exec( $db, $bindings,
            "SELECT `".implode("`, `", self::pluck($columns, 'db'))."`
             FROM `$table`
             $where
             $order
             $limit"
        );

to:

        // Main query to actually get the data
        $data = self::sql_exec( $db, $bindings,
            "SELECT LOWER(CONCAT_WS(' ' , GivName,MidName,Surname)) AS fullname, `GivName`, `Surname`, `PhoneHome`, `CellPhone`, `Email`, `City`, `WifesName`, `SrSysID`
             FROM `$table`
             $where
             $order
             $limit"
        );

Again, this is not exactly the solution that I'm looking for, but in the end it does produce the desired result.

For some of my tables this will suffice... for other more complex tables, I'm still plugging away at a "smart search" solution.

Hope this helps someone.

trevoc

NOTE: this wasn't meant as a step by step guide....

to work, you'll either have to create a view, or other columns programatically (expression) that also enables DT to search for these "Search" columns.