/** * Updates the total count of models. For better performance * the count is only updated on edges, which is when new models * need to be loaded. */ private function updateCount() { // The count only needs to be updated when the pointer is // on the edges if ($this->pointer % $this->limit != 0 && $this->pointer < $this->count) { return; } $model = $this->query->getModel(); $newCount = $model::totalRecords($this->query->getWhere()); // It's possible when iterating over models that something // is modified or deleted that causes the model count // to decrease. If the count has decreased then we // shift the pointer to prevent overflow. // This calculation is based on the assumption that // the first N (count - count') models are deleted. if ($this->count != 0 && $newCount < $this->count) { $this->pointer = (int) max(0, $this->pointer - ($this->count - $newCount)); } // If the count has increased then the pointer is still // valid. Update the count to include the extra models. $this->count = $newCount; }
/** * Build SQL WHERE clause * @param Query $query * @param &array $params * @return string */ protected function buildWhereClause(Query $query, &$params) { $where = ''; foreach ($query->getWhere() as $condition) { $where .= "\n\tAND (" . $condition[0] . ')'; if (!empty($condition[1])) { $params = array_merge($params, $condition[1]); } } if ($where) { return "\nWHERE\n\t" . substr($where, 6); // replace first AND with WHERE } else { return ''; } }
public function evaluateQuery(Query $query) { $select = $this->evaluateSelect($query->getSelect()) . " "; $from = $this->evaluateFrom($query->getFrom()) . " "; $where = $this->evaluateWhere($query->getWhere()) . " "; $order = $this->evaluateOrder($query->getOrder()) . " "; $groupBy = $this->evaluateGroupBy($query->getGroupBy()) . " "; $limit = ''; if ($query->hasLimit()) { $limit = 'LIMIT ' . $query->getLimitMax() . ' ' . $query->getLimitOffset() . ' '; // http://www.postgresql.org/docs/8.1/static/queries-limit.html } return $select . $from . $where . $order . $limit . $groupBy; }
public function evaluateQuery(Query $query) { $select = $this->evaluateSelect($query->getSelect()) . ' '; $from = $this->evaluateFrom($query->getFrom()) . ' '; $where = $this->evaluateWhere($query->getWhere()) . ' '; $order = $this->evaluateOrder($query->getOrder()) . ' '; $groupBy = $this->evaluateGroupBy($query->getGroupBy()) . ' '; $limit = ''; if ($query->hasLimit()) { $limit = 'LIMIT ' . $query->getLimitOffset() . ', ' . $query->getLimitMax() . ' '; // http://www.sqlite.org/lang_select.html } return $select . $from . $where . $order . $limit . $groupBy; }
$params = "'" . implode("','", $params) . "'"; } $this->where($column . ' NOT IN (' . $params . ')'); } public function addBetween($column, $start, $stop) { $this->where($column . ' BETWEEN ' . $start . ' AND ' . $stop); } public function addNotBetween($column, $start, $stop) { $this->addWhere($column . ' BETWEEN ' . $start . ' AND ' . $stop); } /** * Add join * * @access public * @param string $table table name * @param string $condition condition to search * @param string $type type join * @return void */ public function addJoin($table, $condition, $type = 'LEFT') { $this->join .= ' ' . $type . ' JOIN ' . $table . ' ON ' . $condition; } } $query = new Query(); $query->where('id=40'); $query->where('put<30'); echo $query->getWhere();
public function evaluateQuery(Query $query) { $select = $this->evaluateSelect($query->getSelect()) . ' '; $from = ''; $order = $this->evaluateOrder($query->getOrder()) . ' '; $groupBy = $this->evaluateGroupBy($query->getGroupBy()) . ' '; // SQLServer 2008 no tiene LIMIT, hay que hacerlo con una subquery usando ROWNUM // Soluciona problema de paginacion en SQLServer, como esta en DAL::listAll() // if ($query->hasLimit()) { // FIXME: // El filtro del where que no es el filtro por rowNum // debe hacerse en la consulta interna, sino se toman // rowNums en la consulta interna que luego no estan // en la consulta final, eso afecta al resultado paginado, // porque se pagina por rowNum de rows que no matchean el where, ej: // - consulta interna devuelve 1,2,3,4,5,6,7,8 // - consulta externa tiene max=3, offset=0 // - where matchea solo 2,4,5,7 // - el resultado es solo 2, en lugar de 2,4,5 (son los primeros 3 que matchean) // // problema, el where incluye condiciones que no son sobre la tabla utilizada // en la consulta interna, esto afecta? $tableName = $query->getLimitTable(); $offset = $query->getLimitOffset(); $max = $query->getLimitMax(); // La consulta interna es para hacer paginacion // WHERE: Las condiciones donde dice tableName pone T2 (no puede evaluar condiciones sobre atributos de tablas no mencionados en el FROM) // - http://social.msdn.microsoft.com/Forums/sqlserver/en-US/3b2e0875-e98c-4931-bcb4-e9f449b637d7/the-multipart-identifier-aliasfield-could-not-be-bound // Hago el evaluate del from aca porque tengo que cambiar el FROM de tableName por la subconsulta necesaria para el limit $queryFrom = $query->getFrom(); $alias; if (count($queryFrom) == 0) { // ERROR! es obligatorio por lo menos una! throw new Exception("FROM no puede ser vacio"); } else { /* $res = "FROM "; foreach ($queryFrom as $table) { if ( $table->name == $tableName ) { $alias = $table->alias; $res .= '( SELECT ROW_NUMBER() OVER (ORDER BY id) AS rowNum, * '. 'FROM '. $tableName . $this->evaluateWhere( $query->getWhere() ) . ' ) '. $alias .', '; } else { $res .= $table->name .' '. $table->alias .', '; } } $from = substr($res, 0, -2) .' '; */ $alias = 'subq'; // * selecciona multiples columnas con mismo nombre en distintas tablas $subquery = '( ' . $select . ', ROW_NUMBER() OVER (ORDER BY ' . $tableName . '.id) AS rowNum ' . $this->evaluateFrom($query->getFrom()) . ' ' . $this->evaluateWhere($query->getWhere()) . ' ' . $order . $groupBy . ' ) ' . $alias . ' '; $from = 'FROM ' . $subquery; $select = 'SELECT * '; // Select para la query ppal. agregaciones y group se harian en la subquery } $where = 'WHERE ' . $alias . '.rowNum-1 >= ' . $offset . ' AND ' . $alias . '.rowNum-1 < ' . ($offset + $max); return $select . $from . $where; } // hasLimit // !hasLimit $from = $this->evaluateFrom($query->getFrom()) . ' '; $where = $this->evaluateWhere($query->getWhere()) . ' '; return $select . $from . $where . $order . $groupBy; }