yf_table2_filter classTable2 plugin.
| File | plugins/table2/classes/table2/yf_table2_filter.class.php |
|---|---|
| Use | _class('table2_filter', 'classes/table2/') |
A site sets them in its config with $PROJECT_CONF['table2_filter']['NAME'] = ...;
| Property | Default | Description |
|---|---|---|
$supported_conds |
[] |
None without a leading underscore.
_ hooks and helpers; not routable as page actionspublic function _escape_values() public function _escape_values()
{
return (bool) _class('table2')->ESCAPE_FILTER_VALUES;
}
public function _filter_array(&$data, $filter = [], $filter_params = [], $table = null)Simple filtering of the given array. Need to support table() raw array data with filtering.
public function _filter_array(&$data, $filter = [], $filter_params = [], $table = null)
{
if ( ! $data || ! $filter) {
return false;
}
foreach ((array) $data as $_id => $_data) {
foreach ((array) $filter as $fk => $fv) {
if (isset($_data[$fk]) && strlen($fv)) {
if (is_array($_data[$fk])) {
if (isset($filter_params[$fk])) {
$fp = $filter_params[$fk];
foreach ((array) $_data[$fk] as $k2 => $v2) {
if ($fp == 'like') {
if (false === strpos($_data[$fk][$k2], $fv)) {
unset($data[$_id]);
continue 3;
}
} elseif ($fp == 'eq') {
if ($_data[$fk][$k2] != $fv) {
unset($data[$_id]);
continue 3;
}
} elseif ($fp == 'ne') {
if ($_data[$fk][$k2] == $fv) {
unset($data[$_id]);
continue 3;
}
} elseif ($fp == 'gt') {
if ($_data[$fk][$k2] <= $fv) {
unset($data[$_id]);
continue 3;
}
} elseif ($fp == 'gte') {
if ($_data[$fk][$k2] < $fv) {
unset($data[$_id]);
continue 3;
}
} elseif ($fp == 'lt') {
if ($_data[$fk][$k2] >= $fv) {
unset($data[$_id]);
continue 3;
}
} elseif ($fp == 'lte') {
if ($_data[$fk][$k2] > $fv) {
unset($data[$_id]);
continue 3;
}
} elseif ($fp == 'rlike') {
// TODO
} elseif ($fp == 'between') {
// TODO
}
}
} elseif ( ! isset($_data[$fk][$fv])) {
unset($data[$_id]);
continue 2;
}
} else {
if (isset($filter_params[$fk])) {
$fp = $filter_params[$fk];
if ($fp == 'like') {
if (false === strpos($_data[$fk], $fv)) {
unset($data[$_id]);
continue 2;
}
} elseif ($fp == 'eq') {
if ($_data[$fk] != $fv) {
unset($data[$_id]);
continue 2;
}
} elseif ($fp == 'ne') {
if ($_data[$fk] == $fv) {
unset($data[$_id]);
continue 2;
}
} elseif ($fp == 'gt') {
if ($_data[$fk] <= $fv) {
unset($data[$_id]);
continue 2;
}
} elseif ($fp == 'gte') {
if ($_data[$fk] < $fv) {
unset($data[$_id]);
continue 2;
}
} elseif ($fp == 'lt') {
if ($_data[$fk] >= $fv) {
unset($data[$_id]);
continue 2;
}
} elseif ($fp == 'lte') {
if ($_data[$fk] > $fv) {
unset($data[$_id]);
continue 2;
}
} elseif ($fp == 'rlike') {
// TODO
} elseif ($fp == 'between') {
// TODO
}
} elseif ($_data[$fk] != $fv) {
unset($data[$_id]);
continue 2;
}
}
}
}
}
}
public function _filter_sql_prepare($filter_data = [], $filter_params = [], $__sql = '', $table = null) public function _filter_sql_prepare($filter_data = [], $filter_params = [], $__sql = '', $table = null)
{
if ( ! $filter_data) {
return '';
}
if (is_null($table) || ($table?->_params['db'] ?? false) || ! is_object($table->_params['db'])) {
$db = db();
} else {
$db = $table->_params['db'];
}
$special_fields = [
'order_by',
'order_direction',
];
$array_fields = [
'in_array',
];
$supported_conds = &$this->supported_conds;
$sql = null;
$filter_sql = null;
$order_sql = null;
foreach ((array) $filter_data as $k => $v) {
if ( ! strlen($k)) {
continue;
}
if (in_array($k, $special_fields)) {
continue;
}
// Special field for BETWEEN second value
if (substr($k, -strlen('__and')) == '__and') {
$k = substr($k, 0, -strlen('__and'));
$from = $filter_data[$k];
if ($from) {
continue;
}
$v = ' ';
}
$field = $k;
$left_part = '';
$part_on_the_right = '';
// Here we support complex filtering conditions, examples:
// 'price' => array('gt', 'value' => '100')
// 'price' => array('between', 'value' => '1', 'and' => '10')
// 'name' => array('like', 'value' => 'john')
if (is_array($v) && ! (@is_array($filter_params[$k]) && @in_array($filter_params[$k]['cond'], $array_fields))) {
$cond = isset($v[0]) ? $v[0] : $v['cond'];
if ( ! $cond) {
continue;
}
if ( ! isset($supported_conds[$cond])) {
continue;
}
if ( ! isset($v['and']) && $this->_escape_values()) {
$v['and'] = $filter_data[$k . '__and'] ?? null;
}
if ($v['field']) {
$field = $v['field'];
}
$part_on_the_right = $supported_conds[$cond]($v);
} else {
if (@is_array($filter_params[$k]) && @in_array($filter_params[$k]['cond'], $array_fields)) {
$v = implode(',', array_values($v));
}
if ( ! strlen($v)) {
continue;
}
$cond = 'eq';
$func = null;
// Here we can override default 'eq' condition with custom one by passing filter $params like this: table($sql, array('filter_params' => $filter_params)).
$field_params = $filter_params[$k];
if ($field_params) {
// Fully replacing left and right parts with callback function
// Example: table($sql, array('filter_params' => array('my_field' => function($a){ return ' v.value LIKE "%'._es($a['value']).'%" '; } )))
if (is_callable($field_params)) {
$left_part = ' ';
$func = $field_params;
// Ways of passing array of params: 1) long and 2) short
// Example: table($sql, array('filter_params' => array('value' => array('cond' => 'like', 'field' => 'v.value'))))
// Example: table($sql, array('filter_params' => array('translation' => array('like', 't.value'))))
} elseif (is_array($field_params)) {
$cond = isset($field_params['cond']) ? $field_params['cond'] : $field_params[0];
if ( ! $cond) {
$cond = 'eq';
}
$func = $supported_conds[$cond];
if ($field_params['field']) {
$field = $field_params['field'];
} elseif (isset($field_params[1])) {
$field = $field_params[1];
}
// Predefined condition found (gt, between, like, etc..)
// Example: table($sql, array('filter_params' => array('name' => 'gt', 'price' => 'between')))
} elseif (isset($supported_conds[$field_params])) {
$cond = $field_params;
$func = $supported_conds[$cond];
}
} else {
$func = $supported_conds[$cond];
}
// Field with __and on the end of its name is special one for 'between' condition
if ($func) {
$part_on_the_right = $func(['value' => $v, 'and' => ($filter_data[$k . '__and'] ?? null)], $filter_data);
}
}
if ( ! strlen($left_part)) {
if ($this->_escape_values() && ! $this->_is_identifier($field)) {
continue;
}
$left_part = '`' . str_replace('.', '`.`', $db->es($field)) . '`';
}
if ($part_on_the_right) {
$sql[] = trim($left_part) . ' ' . trim($part_on_the_right);
}
}
if ($sql) {
$filter_sql = ' AND ' . implode(' AND ', $sql);
}
if (isset($filter_data['order_by']) && $filter_data['order_by'] && strpos(strtoupper($__sql), 'ORDER BY') === false) {
$order_by_field = $filter_data['order_by'];
if (is_array($filter_params[$order_by_field])) {
$field_params = $filter_params[$order_by_field];
if ($field_params['field']) {
$order_by_field = $field_params['field'];
} elseif (isset($field_params[1])) {
$order_by_field = $field_params[1];
}
}
if ($this->_escape_values() && ! $this->_is_identifier($order_by_field)) {
return [$filter_sql, $order_sql];
}
$order_sql = ' ORDER BY `' . str_replace('.', '`.`', $db->es($order_by_field)) . '` ';
$direction = null;
if ($filter_data['order_direction']) {
$direction = strtoupper($filter_data['order_direction']);
}
if ($direction && in_array($direction, ['ASC', 'DESC'])) {
$order_sql .= ' ' . $direction;
}
}
return [$filter_sql, $order_sql];
}
public function _in_list($value)in / in_array list: today _es() of the whole string without quotes, which does not stop "1) OR (1=1"; with yf_table2::$ESCAPE_FILTER_VALUES every comma separated item is escaped and quoted.
public function _in_list($value)
{
if ( ! $this->_escape_values()) {
return _es($value);
}
$items = [];
foreach ((array) (is_array($value) ? $value : explode(',', (string) $value)) as $item) {
$items[] = '"' . _es(trim((string) $item)) . '"';
}
return implode(',', $items);
}
public function _init() public function _init()
{
$this->supported_conds = [
'in' => function ($a) {
return ' IN( ' . $this->_in_list($a['value']) . ' )';
}, // "in"
'in_array' => function ($a) {
return ' IN( ' . $this->_in_list($a['value']) . ' )';
}, // "in array"
'eq' => function ($a) {
return ' = "' . _es($a['value']) . '"';
}, // "equal"
'ne' => function ($a) {
return ' != "' . _es($a['value']) . '"';
}, // "not equal"
'gt' => function ($a) {
return ' > "' . _es($a['value']) . '"';
}, // "greater than",
'gte' => function ($a) {
return ' >= "' . _es($a['value']) . '"';
}, // "greater or equal than",
'lt' => function ($a) {
return ' < "' . _es($a['value']) . '"';
}, // "less than",
'lte' => function ($a) {
return ' <= "' . _es($a['value']) . '"';
}, // "lower or equal than"
'like' => function ($a) {
return ' LIKE "%' . _es($a['value']) . '%"';
}, // LIKE '%'.$value.'%'
'rlike' => function ($a) {
return ' RLIKE "' . _es($a['value']) . '"';
}, // regular expression, RLIKE $value
'dt_eq' => function ($a) {
return ' = "' . _es(strtotime($a['value'])) . '"';
}, // "equal"
'dt_ne' => function ($a) {
return ' != "' . _es(strtotime($a['value'])) . '"';
}, // "not equal"
'dt_gt' => function ($a) {
return ' > "' . _es(strtotime($a['value'])) . '"';
}, // "greater than",
'dt_gte' => function ($a) {
return ' >= "' . _es(strtotime($a['value'])) . '"';
}, // "greater or equal than",
'dt_lt' => function ($a) {
return ' < "' . _es(strtotime($a['value'])) . '"';
}, // "less than",
'dt_lte' => function ($a) {
return ' <= "' . _es(strtotime($a['value'])) . '"';
}, // "lower or equal than"
'between' => function ($a) {
$from = trim($a['value']);
$to = trim($a['and'] ?? '');
$result = null;
if ($from && $to) {
if ($from > $to) {
$tmp = $from;
$from = $to;
$to = $tmp;
}
$result = sprintf(' BETWEEN "%s" AND "%s"', $this->_value($from), $this->_value($to));
} elseif ($from) {
$result = sprintf(' >= "%s"', $this->_value($from));
} elseif ($to) {
$result = sprintf(' <= "%s"', $this->_value($to));
}
return $result;
},
'dt_between' => function ($a) {
$from = trim($a['value']);
$to = trim($a['and'] ?? '');
! empty($from) && $from = strtotime($from);
! empty($to) && $to = strtotime($to);
$result = null;
if ($from && $to) {
if ($from > $to) {
$tmp = $from;
$from = $to;
$to = $tmp;
}
$result = sprintf(' BETWEEN "%s" AND "%s"', $from, $to);
} elseif ($from) {
$result = sprintf(' >= "%s"', $from);
} elseif ($to) {
$result = sprintf(' <= "%s"', $to);
}
return $result;
},
'datetime_between' => function ($a) {
$from = trim($a['value']);
$to = trim($a['and'] ?? '');
$format = 'Y-m-d H-i-s';
! empty($from) && $from = date($format, strtotime($from));
! empty($to) && $to = date($format, strtotime($to));
$result = null;
if ($from && $to) {
if ($from > $to) {
$tmp = $from;
$from = $to;
$to = $tmp;
}
$result = sprintf(' BETWEEN "%s" AND "%s"', $from, $to);
} elseif ($from) {
$result = sprintf(' >= "%s"', $from);
} elseif ($to) {
$result = sprintf(' <= "%s"', $to);
}
return $result;
},
'daterange_between' => function ($a) {
$value = $a['value'];
if ( ! $value || false === strpos($value, '-')) {
return false;
}
$only_date_regex = '~^[0-9]{2,4}[\.-][0-9]{2,4}[\.-][0-9]{2,4}$~';
list($from, $to) = array_pad(explode(' - ', $value), 2, '');
$from = trim($from);
if (preg_match($only_date_regex, $from)) {
$from .= ' 00:00:00';
}
$to = trim($to);
if (preg_match($only_date_regex, $to)) {
$to .= ' 23:59:59';
}
! empty($from) && $from = strtotime(str_replace('.', '-', $from));
! empty($to) && $to = strtotime(str_replace('.', '-', $to));
$result = null;
if ($from && $to) {
if ($from > $to) {
$tmp = $from;
$from = $to;
$to = $tmp;
}
$result = sprintf(' BETWEEN "%s" AND "%s"', $from, $to);
} elseif ($from) {
$result = sprintf(' >= "%s"', $from);
} elseif ($to) {
$result = sprintf(' <= "%s"', $to);
}
return $result;
},
'daterange_dt_between' => function ($a) {
$value = $a['value'];
if ( ! $value || false === strpos($value, '-')) {
return false;
}
$only_date_regex = '~^[0-9]{2,4}[\.-][0-9]{2,4}[\.-][0-9]{2,4}$~';
list($from, $to) = array_pad(explode(' - ', $value), 2, '');
$from = trim($from);
if (preg_match($only_date_regex, $from)) {
$from .= ' 00:00:00';
}
$to = trim($to);
if (preg_match($only_date_regex, $to)) {
$to .= ' 23:59:59';
}
$format = 'Y-m-d H-i-s';
! empty($from) && $from = date($format, strtotime(str_replace('.', '-', $from)));
! empty($to) && $to = date($format, strtotime(str_replace('.', '-', $to)));
$result = null;
if ($from && $to) {
if ($from > $to) {
$tmp = $from;
$from = $to;
$to = $tmp;
}
$result = sprintf(' BETWEEN "%s" AND "%s"', $from, $to);
} elseif ($from) {
$result = sprintf(' >= "%s"', $from);
} elseif ($to) {
$result = sprintf(' <= "%s"', $to);
}
return $result;
},
];
}
public function _is_identifier($name)Field and order_by names come from the posted filter and go into backticks, which es() does not escape.
public function _is_identifier($name)
{
return is_string($name) && preg_match('~^[a-z0-9_.]+\z~i', $name);
}
public function _value($value)between value inside double quotes: escaped with yf_table2::$ESCAPE_FILTER_VALUES, raw otherwise.
public function _value($value)
{
return $this->_escape_values() ? _es($value) : $value;
}