adding where BETWEEN support to Editor search for serverside range filtering

sinfuljosh

First I want to say THANK YOU about the recent Editor updates that include the ability to read from readonly tables like views as this helps me tons while trying to update the outdated badly written schema I am trying to bring current.

I recently was looking for a server side range search to perform filtering using the Editors API's in the same way the .search is used. I am not the best programmer but I was able to get a date range filter using the datetime type by adding some new array values to the data submitted, and adding a new logic to the editor.php's search function that sees the new data, splits it, and filters as a range (similar to the client side's filter range example)

In looking at the editors.php search function I noticed it is a bit limited to LIKE with wildcard and was curious if anyone has thought about adding a new where function for BETWEEN similar to this.

->whereBetween($column, ['minVal','maxVal'])

Or is there already a means of doing this that I am missing. because most of the references to range filtering has been limited to client side only or their own custom methods since the search api looks limited to LIKE with wildcard wrappings.

allan

Awesome - great to hear that is a useful addition

There isn't a whereBetween (good idea though) but you can use a raw condition - e.g.

$q->where( $column, '(minValue, MaxValue'), 'BETWEEN', false );

The syntax isn't ideal for this, but that should do the job (obviously you'd need to update it to actually pass in the min and max values, and it should be bound as shown in the documentation I linked to above).

Allan

sinfuljosh

With the above where statement, this would not create the same end user functionality that happens when someone searches using the search input, right?

Meaning that It unless I include additional code to return the filtered count and the total count.
Then it will only return the total count (because the above where statement would essentially by performed at the root level of the query.
This was the issue I was hitting when I had tried to use a where statement as an additional line on the Editor php file for that table.

Instead I modified the Editor.php's _ssp_filter function slightly to check for a new value in the columns.search data called range that was either true or false.
And added to the ajax data: to manually set the "range" to true for the column that would be a range filter

this let me use the column().search().draw() method the same way the search field filtering works, I just had to join the min and max ranges by a '-'.

This let me have customer column based range filters for my datatable that filtered a single column an returns the proper counts (filtered and totals) when using server side

which is great when working with 300,000+ records and trying to keep the searching and filtering as dynamic as possible.