Exact match regex, serverSide:true, and Editor

Loren Maxwell

I'm working on an autocomplete filter for a column where I'd like the table to filter on an exact match when the user "selects" a specific entry from the autocomplete.

Consider this page, the scoreboard: http://cfbha.org/w/Special:SeasonHome?view=scoreboard

If I type in "Georgia" into the school filter, I get Georgia, Georgia Southern, Georgia State, and Georgia Tech in the autocomplete and West Georgia shows up in the table as well, which is fine.

If I want only the Georgia games themselves, then I select "Georgia" from the autocomplete by either clicking on it or arrow-keying to it and pressing enter.

"Selecting" an actual option from the autocomplete adds "^" to the front and "$" on the back, uses the DataTables search with regex = true, and reduces the table just to the Georgia games themselves.

Perfect!

However, on another page I have a table with 645,000 rows so I use serverSide: true: http://cfbha.org/w/Special:DatabaseHome?view=tn_g

Typing "Georgia" here gives Georgia, Georgia Southern, Georgia Southwestern, Georgia State, Georgia State College, and Georgia Tech in the autocomplete while all those plus West Georgia, West Georgia JC, and even tiny Middle Georgia State show up in the table. All good so far.

But if I select "Georgia" from the autocomplete here then the table returns no rows, partially because I've added the "^" and "$" but mostly because server side processing doesn't support regex.

I've scoured the forums and continually see that I have to account for this on the server side script.

I'm using Editor to retrieve the records, so it seems like a ->where() condition would be applied when an option from the autocomplete is "selected".

I also know which fields to do the exact match on because the search terms have had "^" and "$" appended to them on the client side.

Using $_POST['columns'], I've made a function to build the ->where() condition based on which columns have been passed a search value with the "^" and the "$":

    ->where( function ( $q ) {
        $columns = $_POST['columns'];
        $filters = 0;
        foreach ($columns as $column) {
            $columnName = $column['name'];
            $searchString = $column['search']['value'];
            if (substr($searchString, 0, 1) == '^' && substr($searchString, -1) == '$') {
                ++$filters;
                $actualSearchString = substr($searchString, 1, -1);
                if ($filters == 1) {
                    $q->where( $columnName, $actualSearchString);
                } else {
                    $q->and_where( $columnName, $actualSearchString);
                }
            }
        };
    })

However I get no results returned.

It appears the query is being built properly. I can do a var_dump($q) and it looks identical to simply using ->where('team_name', 'Georgia').

However I suspect somewhere Editor is still referring back to that initial passed string of "^Georgia$" and trying to filter on that.

Am I correct, and if so how can I catch and update that $_POST['columns'] before it gets passed?

Or if I'm not correct where should I be looking?

rf1234

Hi Loren,
just a couple of thoughts, hopefully helpful. Search on the client side works with what is returned from the server. Server side search works with what is returned from the database. That can be quite a difference. I faced this challenge when it comes to search for dates. English UK and German dates are not in YYYY-MM-DD format. So entering a date into the search field when "server side " processing is being activated does not work because nothing matches.

For that reason I had to devise this rather complex stuff to make it work for English UK and German users (Americans will hate it because we both have DD MM YYYY logic and we don't want the month first ...). German format is DD.MM.YYYY, UK format is DD/MM/YYY, and I tried to be as flexible as possible regarding the format that users enter the date. For that reason I needed to make sure that a number entry isn't erroneously taken for a date. UK: decimal point. Germany: decimal comma ...

This is the example. Hopefully it helps ...

var forexTable = $('#tblForex').DataTable( {
    dom: "Bfrltip",
    processing: true,
    serverSide: true,    //server side only works well with type "POST" !!!
    ajax: {
        url: 'actions.php?action=tblForex',
        type: 'POST',
        data: function ( d ) {
    //allow searching for dates with server side processing
            var dFs = ['D/M/YYYY', 'DD/MM/YYYY', 'D/M/YY', 'DD/MM/YY', 'D/M', 'DD/MM', 'D/M/', 'DD/MM/'];
            var toBeFormat = ''; var sepCounter = 0;
    //No commas allowed as date separator; if English: no period either!
            if ( ( ! ( d.search.value.indexOf(',') >= 0 ) ) &&
                 ( ! ( lang !== 'de' && d.search.value.indexOf('.') >= 0 ) )    )   {
                if ( moment(d.search.value, dFs).isValid() ) {
                    toBeFormat = 'MM-DD';
                    for (i=0; i < d.search.value.length; i++) {
                        //counting the separators in the search string
                        if ( isNaN (d.search.value.substr(i, 1)) ) {
                            sepCounter++;
                    //if we find two separators and the second one is not at the
                    //end of the string we have a long date otherwise only a short one
                            if ( sepCounter === 2 && i < (d.search.value.length-1) ) {
                                toBeFormat = 'YYYY-MM-DD';
                            }
                        }                        
                    }
                    if (sepCounter > 0) { //we need to have found at least one separator
                        d.search.value = moment(d.search.value, dFs).format(toBeFormat);
                    } else {
                        toBeFormat = '';
                    }
                }
            }
    //not a date: we check for a number
            if (toBeFormat <= '') {
                var number;
                if (lang == 'de') {
                    number = d.search.value.toString().replace( /[\.]/g, "" );
                    number = d.search.value.toString().replace( /[\,]/g, "." );
                } else {
                    number = d.search.value.toString().replace( /[\,]/g, "" );
                }
                if ( ! isNaN( parseFloat(number) ) ) {             
                    d.search.value = number;
                }
            }
        }
    },
    pageLength: 20,
    lengthMenu: [5, 10, 20, 50, 100, 200, 500],
    columns: [
        {   data: "forex.currency" },
        {   data: "forex.date" },
        {   data: "forex.rate" },
        {   data: "forex.update_time" }
    ],
    order: [[ 1, 'desc' ]],
    select: {
        style: 'single'
    },            
    buttons: [
        {   extend: "create", editor: forexEditor },
        {   extend: "edit",   editor: forexEditor },
        {   extend: "remove", editor: forexEditor },
                    "colvis"
    ]
} );

allan

However I suspect somewhere Editor is still referring back to that initial passed string of "^Georgia$" and trying to filter on that.

Correct. I'm afraid that Editor's server-side processing doesn't currently support regex searching. I've found the performance of it to be way too slow to used in production.

What I would suggest here, rather than using the column().search() method, is to submit the search string as a custom value (which you can do using ajax.data). Then use ->where() has you have been, which will do an exact match by default.

Server side search works with what is returned from the database.

I should clarify this. Its "sort of" correct . The search is actually done by the database itself through the WHERE condition. So yes, it is what is returned by the table, but not by the database as such.

Allan

Loren Maxwell

Thanks, @rft1234 and @allan.

You've both given me something to look at today and I'll report back.

@allan, after doing some research, what is the reason that ajax.params() cannot update the parameters?

Looking at the code (lines 7598 through 7609) and comparing it to ajax.url() (lines 7628 to 7669), It seems like it would be just a relatively small change, but it would make ajax.reload() much more powerful, particularly in cases like the one I'm asking about where I'd like to be able to simply set the parameters and reload the table.

It would also allow a reduction in the complexity of the server side code.

Finally, this approach would also nicely parallel the search() and draw() pattern when not using server side processing.

Is there something I'm overlooking?

rf1234

@allan so you are really doing the search using the database itself? I thought the data was returned from the database (based on the original SQL statement that may or may not include a where clause) into an array and you'd search the resulting array before getFormatting is being done.
That would mean you are generating additional SQL based on the entered search string.
Am I getting it right?

allan

what is the reason that ajax.params() cannot update the parameters?

It gets the parameters that were sent to the server in the last request. Its a getter only, not a setter.

The reason I've not introduced the ability to make it a setter is that I was worried about the API method being used in multiple places - should it combine previous parameters sent to it, or should it take just the current arguments. A case could be made for both. ajax.data is a single point where the parameters can be controlled.

so you are really doing the search using the database itself?

Yes. If you have server-side processing enabled, it will include a WHERE condition if there is a condition sent by the client-side.

and you'd search the resulting array before getFormatting is being done.

Correct, and that's the major disadvantage of this approach. On the other hand it means that it is the SQL engine which does the search which it is optimised to do with a WHERE statement. Doing in PHP (or whatever) would mean reading all of the records out of the database on every request and then throwing away the ones we don't want. That would incur a huge performance penalty with large data sets.

Allan

Loren Maxwell

@allan,

The reason I've not introduced the ability to make it a setter is that I was worried about the API method being used in multiple places - should it combine previous parameters sent to it, or should it take just the current arguments. A case could be made for both.

I see the issue there.

For this, I chose to manage the parameters by creating two plugins, one for adding a parameter (or parameters) and one for deleting a parameter (could easily be improved to delete an array of parameters):

                    /**
                     * Add a parameter to the current Ajax data.
                     *
                     * @params {string} Parameter(s) to add.
                     * @returns {DataTables.Api} this
                     */
                    $.fn.dataTable.Api.register( 'ajax.addParam()', function ( params ) {

                        /* add */
                        return this.iterator( 'table', function ( settings ) {
                            if ( $.isPlainObject( settings.ajax ) ) {
                                $.extend(settings.ajax.data, params);
                            }
                            else {
                                /* ? */
                            }
                        } );
                    } );

                    /**
                     * Delete a parameter from the current Ajax data.
                     *
                     * @paramKey {string} Parameter key to delete.
                     * @returns {DataTables.Api} this
                     */
                    $.fn.dataTable.Api.register( 'ajax.deleteParam()', function ( paramKey ) {

                        /* delete */
                        return this.iterator( 'table', function ( settings ) {
                            if ( $.isPlainObject( settings.ajax ) ) {
                                delete settings.ajax.data[paramKey];
                            }
                            else {
                                /* ? */
                            }
                        } );
                    } );

I'm not sure what to do if ($.isPlainObject(settings.ajax)) is false . . .

Anyway, I now have a nice input with autocomplete that filters the table as I type and when a value is selected the table is then filtered down to the exact match:

    function update_topp_newton_games_by_team_name_filter(suggestion, selected) {
        var colname = "team_name:name";
        if (!selected) {
            topp_newton_games.ajax.deleteParam("team_name").ajax.reload().column(colname).search(suggestion).draw();
            $("#topp_newton_games_team_name_filter_wrapper").removeClass("autocomplete-input-selected");
        } else {
            topp_newton_games.ajax.addParam({
                team_name: $("#topp_newton_games_team_name_filter").val()
            }).ajax.reload().column(colname).search(suggestion).draw();
            $("#topp_newton_games_team_name_filter_wrapper").addClass("autocomplete-input-selected");
        };
        editor_topp_newton_games.field("team_name").def(suggestion);
    };

So revisiting my scenario, typing "Georgia" returns a number of schools with "Georgia" in the name but selecting "Georgia" returns the games with an exact match on "Georgia".

Another improvement to the function would be to do away with the second ajax reference in ....ajax.addParam({p:'p'}).ajax.reload() to make it more concise ....ajax.addParam({p:'p'}).reload().

As for using Editor on the server side, I'm able to eliminate the loop through columns I had previously and now have a fairly simple where function that returns null if the "team_name" parameter is not set, thus returning all rows:

    ->where( function ($q) {
        $q->where_group( function ($q2) {
            (isset($_POST['team_name']) ? $q2->where( 'team_name', $_POST['team_name'] ) : null );
        } );
    } )

Finally, there's a lot of information on the site about creating API plugins, but hands down here was the most helpful page on the site: https://datatables.net/examples/plug-ins/api

It has clean, simple, easy to understand code for the JS!

allan

Thanks for the suggestions - that looks great!

I'm not sure what to do if ($.isPlainObject(settings.ajax)) is false . . .

Probably nothing! I don't think there is much your API could do. Throw an error perhaps.

Allan

Loren Maxwell

Thanks, @allan, any advice on how to chain the calls with only one Ajax reference?

allan

You'd need to add a reload() function to your addParam() method - e.g. register( 'ajax.addParam().reload()', function ... );. Have that function simply call ajax.reload() itself.

Allan