Example #1
0
 /**
  * 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;
 }
Example #2
0
 /**
  * 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;
 }
Example #5
0
            $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;
 }