Search operators with server-side (PHP)

Balubaer

Hello,

I was looking around to allow me to implement more or less complex search operators (mostly simple compare operators like <, >, <=, >= etc.) and was trying to implement it in the preGet event.

But implementing this I faced the problem that the origin search parameters of the request cannot be changed/removed, so e.g. implementing a search where the parameters might be something like

columns[2][data]: id
columns[2][name]: id
columns[2][searchable]: true
columns[2][orderable]: true
columns[2][search][value]: > 12965
columns[2][search][regex]: false

and the preGet event code something like

$editor->where(function($q) {
// code shortened
// parsing $_POST parameters
    $field_name = 'id'; // as example
    $operator = '>'; // as example
    $value = '12965'; // as example
    $q->where($field_name, $value, $operator);
// code shortened
});

the original search parameter will prevent a correct result, because the SQL will be something like (excerpt from the debug output)

"bindings": [
    // debug shortened
    {
        "name": ":where_9",
        "type": null,
        "value": "12965"
    },
    {
        "name": ":where_10",
        "type": null,
        "value": "%> 12965%"
    }
],
"query": "SELECT  `id` as 'id' FROM  `projectsoverview` WHERE `id` > :where_9 AND `id` like :where_10  LIMIT 120"

where the original request data is stored in the where_10 clause.

Is there any way to use the server-side lib of editor and implementing a custom search (in the backend)? I know that there will be some filter libs for the frontend and regular expressions, too, but it might be a bit too complex to implement a search with comparing (sql-like) operators with regular expressions.

Any help is appreciated.

Greetings,
Dennis

allan

Hi Dennis,

One quick work around would be to use $_POST['columns'][2]['search']['value'] = ''; once you've parsed it.

Its a bit of a hack, but it makes sense given how you are using the built in parameter (good idea that I like it!).

Allan

Balubaer

Hi Allan,

sorry for the long delay but I wasn't able to test you hint before.

Unfortunately manipulating the $_POST variable won't work. As I can see in the debug JSON response I still have both WHERE conditions set (my custom one and the common one generated by your PHP lib).

Maybe, do you store a copy of the $_POST data before triggering the preGet event and using the copy data to proceed the query? (Just thinking of ideas...)

Do you have any other ideas how to handle it?

Thanks,
Dennis

allan

Hi Dennis,

There shouldn't be a copy of the $_POST array in the Editor libraries.

However, perhaps it would be worth making your own copy:

$post = $_POST;

$post['columns'][...] = ...;

Then use ->process( $post )

Allan

Balubaer

Hi Allan,

thanks for you input.

I'm not sure if the problem lies in the order of tasks, that will process my data.

By now I call the $editor->process($_POST) first and within the then (eventually) triggered preGet event I do the parsing of the data, enhancing the query with $editor->where(...) and removing the parsed, obsolete data.

But it seems to be the wrong way, doesn't it? But how to handle it the correct way then?

I just want (and need) to enhance the query while getting data so enhancing the query before the ->process(...) call (is this possible after all?) will be no real option, won't it?

Any hints are appreciated.

Greetings,
Dennis

allan

I think the issue is that you need to override the default search, which involves modifying the data being sent to the ->process() method.

Regardless of any additional query that you add in preGet the original query would still be added if it is in the data set. It needs to be removed from the data.

Allan