/**
* Process
*
* @param array $settings
* @return \Bluz\Grid\Data
*/
public function process(array $settings = [])
{
// process filters
$where = [];
if (!empty($settings['filters'])) {
foreach ($settings['filters'] as $column => $filters) {
foreach ($filters as $filter => $value) {
if ($filter == Grid\Grid::FILTER_LIKE) {
$value = '%' . $value . '%';
}
$where[] = $column . ' ' . $this->filters[$filter] . ' ' . Proxy\Db::quote($value);
}
}
}
// process orders
$orders = [];
if (!empty($settings['orders'])) {
// Obtain a list of columns
foreach ($settings['orders'] as $column => $order) {
$column = Proxy\Db::quoteIdentifier($column);
$orders[] = $column . ' ' . $order;
}
}
// process pages
$limit = ' LIMIT ' . ($settings['page'] - 1) * $settings['limit'] . ', ' . $settings['limit'];
// prepare query
$type = Proxy\Db::getOption('connect', 'type');
if (strtolower($type) == 'mysql') {
// MySQL
$dataSql = preg_replace('/SELECT\\s(.*?)\\sFROM/is', 'SELECT SQL_CALC_FOUND_ROWS $1 FROM', $this->source, 1);
$totalSql = 'SELECT FOUND_ROWS()';
} else {
// other
$dataSql = $this->source;
$totalSql = preg_replace('/SELECT\\s(.*?)\\sFROM/is', 'SELECT COUNT(*) FROM', $this->source, 1);
if (sizeof($where)) {
$totalSql .= ' WHERE ' . join(' AND ', $where);
}
}
if (sizeof($where)) {
$dataSql .= ' WHERE ' . join(' AND ', $where);
}
if (sizeof($orders)) {
$dataSql .= ' ORDER BY ' . join(', ', $orders);
}
$dataSql .= $limit;
// run queries
// use transaction to avoid errors
Proxy\Db::transaction(function () use(&$data, &$total, $dataSql, $totalSql) {
$data = Proxy\Db::fetchAll($dataSql);
$total = Proxy\Db::fetchOne($totalSql);
});
$gridData = new Grid\Data($data);
$gridData->setTotal($total);
return $gridData;
}
/**
* Find rows by WHERE
* // WHERE alias = 'foo'
* Table::findWhere(['alias'=>'foo']);
* // WHERE alias = 'foo' OR 'alias' = 'bar'
* Table::findWhere(['alias'=>'foo'], ['alias'=>'bar']);
* // WHERE (alias = 'foo' AND userId = 2) OR ('alias' = 'bar' AND userId = 4)
* Table::findWhere(['alias'=>'foo', 'userId'=> 2], ['alias'=>'foo', 'userId'=>4]);
* // WHERE alias IN ('foo', 'bar')
* Table::findWhere(['alias'=> ['foo', 'bar']]);
*
* @param mixed ...$where
* @return array
* @throws \InvalidArgumentException
* @throws Exception\DbException
*/
public static function findWhere(...$where)
{
$self = static::getInstance();
$whereClause = null;
$whereParams = [];
if (sizeof($where) == 2 && is_string($where[0])) {
$whereClause = $where[0];
$whereParams = (array) $where[1];
} elseif (sizeof($where)) {
$whereOrTerms = [];
foreach ($where as $keyValueSets) {
$whereAndTerms = [];
foreach ($keyValueSets as $keyName => $keyValue) {
if (is_array($keyValue)) {
$keyValue = array_map(function ($value) use($self) {
return DbProxy::quote($value);
}, $keyValue);
$keyValue = join(',', $keyValue);
$whereAndTerms[] = $self->table . '.' . $keyName . ' IN (' . $keyValue . ')';
} elseif (is_null($keyValue)) {
$whereAndTerms[] = $self->table . '.' . $keyName . ' IS NULL';
} else {
$whereAndTerms[] = $self->table . '.' . $keyName . ' = ?';
$whereParams[] = $keyValue;
}
if (!is_scalar($keyValue) && !is_null($keyValue)) {
throw new \InvalidArgumentException("Wrong arguments of method 'findWhere'.\n" . "Please use syntax described at https://github.com/bluzphp/framework/wiki/Db-Table");
}
}
$whereOrTerms[] = '(' . implode(' AND ', $whereAndTerms) . ')';
}
$whereClause = '(' . implode(' OR ', $whereOrTerms) . ')';
} elseif (!sizeof($where)) {
throw new DbException("Method `Table::findWhere()` can't return all records from table,\n" . "please use `Table::fetchAll()` instead");
}
return $self->fetch($self->select . ' WHERE ' . $whereClause, $whereParams);
}
