/** * 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); }