YADCF and formatters

nlooije

We have integrated yadcf date range filter into Editor.php of the Datatables Editor PHP library and it works for simple cases.
However for the case where we have to use set/getFormatters for the dates we run into problems.
Is it somehow possible to use the setFormatter to convert the ranges passed by yadcf/dataTables?

// Column filters - Editor.php line 1739
for ( $i=0, $ien=count($http['columns']) ; $i<$ien ; $i++ ) {
    $column = $http['columns'][$i];
    $search = $column['search']['value'];

    if ( $search !== '' && $column['searchable'] == 'true' ) {

        // Added range filter for yadcf
        if (strpos($search, '-yadcf_delim-') !== false) {
            $limits = explode("-yadcf_delim-", $search);
            $limit_low = $limits[0];  // piece1
            $limit_high = $limits[1]; // piece2

                        // Need to convert according to setFormatter format
            $limit_low = (new DateTime($limit_low))->getTimestamp()*1000;
            $limit_high = (new DateTime($limit_high))->getTimestamp()*1000;
            
            if ($limit_low != '' && $limit_high != '') {
                $query->where($this->_ssp_field($http, $i), $limit_low, '>=');
                $query->where($this->_ssp_field($http, $i), $limit_high, '<=');
            } elseif ($limit_low != '') {
                $query->where($this->_ssp_field($http, $i), $limit_low, '>=');
            } elseif ($limit_high != '') {
                $query->where($this->_ssp_field($http, $i), $limit_high, '<=');
            }
        } else {
            $query->where($this->_ssp_field($http, $i), '%' . $search . '%' , 'like');
        }

    }
}

allan

The set formatters will work on the field values only, whereas these values would be coming in from different HTTP variables, so I'm afraid you'd need to add your own functions to deformat the incoming data into the form you need.

Allan

nlooije

Ok, but how are the global search values handled for fields which have get/setFormatters specified? Are they not formatted?
That would mean that global search for fields with get/setFormatters doesn't work.

Currently, I have 'solved' the problem by sending an 'extra' attribute with the column definitions in $_POST and check if it exists. If so, the same function used in setFormatter is used to convert the search string inplace to a search string the query can understand. Only then the process($_POST) call is made.

If needed multiple different formats can be specified

function formatDatetimeToTimestamp($val, $data = null){
    return $val ? (new DateTime($val))->getTimestamp()*1000 : null;
}

// Explicitly handle column filtering formats before processing
foreach ($_POST['columns'] as &$column) {

    if(isset($column['extra']) && $column['search']['value'] != '') {

        $search = &$column['search']['value'];

        switch ($column['extra']) {
            case 'dt_format':

                $delim = '-yadcf_delim-';
                if (strpos($search, $delim) !== false) {
                    $limits = explode($delim, $search);
                }
                $search = implode($delim, 
                    array_map('formatDatetimeToTimestamp', $limits)
                );
                
                break;
            default:
                break;
        }

    }
}

$editor->process( $_POST )->json();

allan

but how are the global search values handled for fields which have get/setFormatters specified? Are they not formatted?

Correct. If you are using server-side processing then it is the search property that is submitted to the server-side that is used for searching. It doesn't go through a formatter.

The method you've used to solve this looks good to me - nice one.

Allan