Server side column filtering with Regular Expressions Regex

dpl

Hi,

i had to add a Checkbox to filter all items > 0 in one column.
Here is my solution, hope, someone find it useful.

<thead>
                        <tr>
                            <th class="hasinput">
                                <input type="text" class="form-control width100" placeholder="Filter" size="10" />
                            </th>
                            <th class="hasinput">
                                <input type="text" class="form-control width100" placeholder="Filter" size="10" />
                            </th>
                            <th class="hasinput">
                                <label class="checkbox-inline"  for="checkstock"> 
                                    <input type="checkbox"  id="checkstock" checked=""> 
                                    Available 
                                </label> 
                            </th>
                            <th></th>
                        </tr>
                        <tr>
                            <th class="text-left">Image</th>
                            <th class="text-left">EAN</th>
                            <th class="text-left">Size</th>
                            <th class="text-left">Total-Stock</th>
                            <th class="text-left">Your Order</th>
                        </tr>
                    </thead>
                    <tbody>
                   
                    </tbody>
                </table>

checkbox is in column 3
if checked, filter all bigger 0
if unchecked filter all

$("#checkstock").on("click", function(e) {
            filterCheckboxAvailability();
        });
        
        function filterCheckboxAvailability(){
            var checked = $('#checkstock').is(':checked');
    
            if (checked) {
                ootable
                    .column( 3+':visible' )
                    .search( '^[1-9][0-9]*$', true )
                    .draw()
            }
            else{
                ootable
                    .column( 3+':visible' )
                    .search( '.', true )
                    .draw()
            }
        }

most important is to edit the standard ssp.class.php on server side as it has no implementation for filtering regular expressions. change function 'filter' to

static function filter ( $request, $columns, &$bindings )
    {
        $globalSearch = array();
        $columnSearch = array();
        $dtColumns = self::pluck( $columns, 'dt' );
        if ( isset($request['search']) && $request['search']['value'] != '' ) {
            $str = $request['search']['value'];
            for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
                $requestColumn = $request['columns'][$i];
                $columnIdx = array_search( $requestColumn['data'], $dtColumns );
                $column = $columns[ $columnIdx ];
                if ( $requestColumn['searchable'] == 'true' ) {
                    $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                    $globalSearch[] = "`".$column['db']."` LIKE ".$binding;
                }
            }
        }
        // Individual column filtering
        if ( isset( $request['columns'] ) ) {
            for ( $i=0, $ien=count($request['columns']) ; $i<$ien ; $i++ ) {
                $requestColumn = $request['columns'][$i];
                $columnIdx = array_search( $requestColumn['data'], $dtColumns );
                $column = $columns[ $columnIdx ];
                $str = $requestColumn['search']['value'];
                if ( $requestColumn['searchable'] == 'true' &&
                 $str != '' && $requestColumn['search']['regex']  == 'false') {
                    $binding = self::bind( $bindings, '%'.$str.'%', PDO::PARAM_STR );
                    $columnSearch[] = "`".$column['db']."` LIKE ".$binding;
                }
                if ( $requestColumn['searchable'] == 'true' &&
                 $str != '' && $requestColumn['search']['regex']  == 'true') {
                    $binding = self::bind( $bindings, $str, PDO::PARAM_STR );
                    $columnSearch[] = "`".$column['db']."` REGEXP ".$binding;
                }
            }
        }
        // Combine the filters into a single string
        $where = '';
        if ( count( $globalSearch ) ) {
            $where = '('.implode(' OR ', $globalSearch).')';
        }
        if ( count( $columnSearch ) ) {
            $where = $where === '' ?
                implode(' AND ', $columnSearch) :
                $where .' AND '. implode(' AND ', $columnSearch);
        }
        if ( $where !== '' ) {
            $where = 'WHERE '.$where;
        }
        return $where;
    }

we added an extra condition. if $requestColumn['search']['regex'] true is passed

dpl

changes were made in row 25-33.
other code is untouched