/** * 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; }
/** * setFromQueryPart * * @param string $table * @return $this */ protected function setFromQueryPart($table) { $table = Db::quoteIdentifier($table); return $this->addQueryPart('from', ['table' => $table], false); }
/** * Set key-value pair * * Sets a new value for a column in a insert/update query * $ub = new UpdateBuilder(); * $ub * ->update('users') * ->set('password', md5('password')) * ->where('id = ?'); * * @param string $key The column to set * @param string $value The value, expression, placeholder, etc * @return Insert|Update */ public function set($key, $value) { $this->setParameter(null, $value, \PDO::PARAM_STR); $key = Db::quoteIdentifier($key); return $this->addQueryPart('set', $key . ' = ?', true); }
/** * Deletes existing rows * * <code> * Table::delete(['login' => 'Man']) * </code> * * @param array $where An array of SQL WHERE clause(s) * @return integer The number of rows deleted * @throws Exception\DbException */ public static function delete(array $where) { if (!sizeof($where)) { throw new DbException("Method `Table::delete()` can't delete all records in table,\n" . "please use `Db::query()` instead (of cause if you know what are you doing)"); } $self = static::getInstance(); $where = $self->filterColumns($where); if (!sizeof($where)) { throw new DbException("Invalid field names of table `{$self->table}`. Please check use of `delete()` method"); } $table = DbProxy::quoteIdentifier($self->table); $sql = "DELETE FROM {$table}" . " WHERE " . join(' AND ', self::prepareStatement($where)); return DbProxy::query($sql, array_values($where)); }