Search operators with server-side (PHP)
Search operators with server-side (PHP)
Balubaer
Posts: 16Questions: 2Answers: 0
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
Replies
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
Hi Allan,
sorry for the long delay but I wasn't able to test you hint before.
Unfortunately manipulating the
$_POSTvariable won't work. As I can see in the debug JSON response I still have bothWHEREconditions set (my custom one and the common one generated by your PHP lib).Maybe, do you store a copy of the
$_POSTdata before triggering thepreGetevent 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
Hi Dennis,
There shouldn't be a copy of the
$_POSTarray in the Editor libraries.However, perhaps it would be worth making your own copy:
Then use
->process( $post )Allan
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) triggeredpreGetevent 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
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
preGetthe original query would still be added if it is in the data set. It needs to be removed from the data.Allan