/** * 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; }
/** * Process * * @param array $settings * @return \Bluz\Grid\Data */ public function process(array $settings = []) { // process filters if (!empty($settings['filters'])) { foreach ($settings['filters'] as $column => $filters) { foreach ($filters as $filter => $value) { if ($filter == Grid\Grid::FILTER_LIKE) { $value = '%' . $value . '%'; } $this->source->andWhere($column . ' ' . $this->filters[$filter] . ' ?', $value); } } } // process orders if (!empty($settings['orders'])) { // Obtain a list of columns foreach ($settings['orders'] as $column => $order) { $this->source->addOrderBy($column, $order); } } // process pages $this->source->setLimit($settings['limit']); $this->source->setPage($settings['page']); // prepare query $type = Proxy\Db::getOption('connect', 'type'); if (strtolower($type) == 'mysql') { // MySQL $select = $this->source->getQueryPart('select'); $this->source->select('SQL_CALC_FOUND_ROWS ' . current($select)); $totalSql = 'SELECT FOUND_ROWS()'; } else { // other $totalSource = clone $this->source; $totalSource->select('COUNT(*)'); $totalSql = $totalSource->getSql(); } $data = []; $total = 0; // run queries // use transaction to avoid errors Proxy\Db::transaction(function () use(&$data, &$total, $totalSql) { $data = $this->source->execute(); $total = Proxy\Db::fetchOne($totalSql); }); $gridData = new Grid\Data($data); $gridData->setTotal($total); return $gridData; }
/** * Get set of records * * @param int $offset * @param int $limit * @param array $params * @param int $total * @return array|int|mixed * @throws ApplicationException */ public function readSet($offset = 0, $limit = 10, $params = [], &$total = null) { $select = $this->getTable()->select(); // switch statement for DB type $type = Proxy\Db::getOption('connect', 'type'); switch ($type) { case 'mysql': $selectPart = $select->getQueryPart('select'); $selectPart = 'SQL_CALC_FOUND_ROWS ' . current($selectPart); $select->select($selectPart); $totalSQL = 'SELECT FOUND_ROWS()'; break; case 'pgsql': default: $selectTotal = clone $select; $selectTotal->select('COUNT(*)'); $totalSQL = $selectTotal->getSql(); break; } $select->setLimit($limit); $select->setOffset($offset); // run queries // use transaction to avoid errors Proxy\Db::transaction(function () use(&$result, &$total, $select, $totalSQL) { $result = $select->execute(); if (!is_null($total)) { $total = Proxy\Db::fetchOne($totalSQL); } }); return $result; }
/** * Return information about tables columns * * @return array */ public function getColumns() { if (empty($this->columns)) { $columns = Cache::get('table:columns:' . $this->table); if (!$columns) { $connect = DbProxy::getOption('connect'); $columns = DbProxy::fetchColumn(' SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?', [$connect['name'], $this->getName()]); Cache::set('table:columns:' . $this->table, $columns); Cache::addTag('table:columns:' . $this->table, 'db'); } $this->columns = $columns; } return $this->columns; }