/** * 查询记录 * * @param string $where * @return array */ protected function _query($where) { $order = $this->_getOrder(); $sql = "SELECT {$this->_columns} FROM {$this->_table} {$where} {$order}"; $offset = $this->_pageSize * ($this->_currentPage - 1); $sql = $this->_db->limit($sql, $this->_pageSize, $offset); return $this->_db->fetchAll($sql); }
/** * Render LIMIT OFFSET clause * * @param string $sql SQL query * @return string */ protected function _renderLimitoffset($sql) { $count = 0; $offset = 0; if (!empty($this->_parts[self::LIMIT_OFFSET])) { $offset = (int) $this->_parts[self::LIMIT_OFFSET]; $count = PHP_INT_MAX; } if (!empty($this->_parts[self::LIMIT_COUNT])) { $count = (int) $this->_parts[self::LIMIT_COUNT]; } /* * Add limits clause */ if ($count > 0) { $sql = trim($this->_adapter->limit($sql, $count, $offset)); } return $sql; }
/** * Render LIMIT OFFSET clause * * @param string $sql SQL query * @return string */ protected function _renderLimitoffset($sql) { $count = 0; $offset = 0; if (!empty($this->_parts[self::LIMIT_OFFSET])) { $offset = (int) $this->_parts[self::LIMIT_OFFSET]; // This should reduce to the max integer PHP can support $count = intval(9223372036854775807); } if (!empty($this->_parts[self::LIMIT_COUNT])) { $count = (int) $this->_parts[self::LIMIT_COUNT]; } /* * Add limits clause */ if ($count > 0) { $sql = trim($this->_adapter->limit($sql, $count, $offset)); } return $sql; }
public function testExceptionInvalidLimitArgument() { $table = $this->getIdentifier(self::TABLE_NAME); $exceptionSeen = false; try { $sql = $this->_db->limit('SELECT * FROM ' . $this->_db->quoteIdentifier($table), 0); } catch (Exception $e) { $this->assertThat($e, $this->isInstanceOf('Zend_Db_Adapter_Exception'), 'Expecting object of type Zend_Db_Adapter_Exception'); $exceptionSeen = true; } $this->assertTrue($exceptionSeen); $exceptionSeen = false; try { $sql = $this->_db->limit('SELECT * FROM ' . $this->_db->quoteIdentifier($table), 1, -1); } catch (Exception $e) { $this->assertThat($e, $this->isInstanceOf('Zend_Db_Adapter_Exception'), 'Expecting object of type Zend_Db_Adapter_Exception'); $exceptionSeen = true; } $this->assertTrue($exceptionSeen); }
/** * Adds an adapter-specific LIMIT clause to the SELECT statement. * * @param mixed $sql sql clause * @param integer $count count * @param integer $offset offset * @return string */ public function limit($sql, $count, $offset = 0) { return $this->_adapter->limit($sql, $count, $offset); }
/** * Converts this object to an SQL SELECT string. * * @todo use $this->_adapter->quoteColumns() for non-PDO adapters * @todo use $this->_adapter->quoteTableNames() for non-PDO adapters * @todo use prepared queries for PDO adapters instead of constructing all the SQL ourselves * like in Adapter/Abstract.php.html:query() * @return string This object as a SELECT string. */ public function __toString() { // initial SELECT [DISTINCT] [FOR UPDATE] $sql = "SELECT"; if ($this->_parts[self::DISTINCT]) { $sql .= " DISTINCT"; } if ($this->_parts[self::FOR_UPDATE]) { $sql .= " FOR UPDATE"; } $sql .= "\n\t"; // add columns if ($this->_parts[self::COLUMNS]) { $columns = array(); foreach ($this->_parts[self::COLUMNS] as $correlationName => $columnList) { foreach ($columnList as $alias => $column) { if (!is_string($alias)) { $alias = null; } if ($column instanceof Zend_Db_Expr) { $columns[] = $this->_adapter->quoteColumnAs($column, $alias); } else { if ($column == '*') { $column = new Zend_Db_Expr('*'); $alias = null; } if (empty($correlationName)) { $columns[] = $this->_adapter->quoteColumnAs($column, $alias); } else { $columns[] = $this->_adapter->quoteColumnAs(array($correlationName, $column), $alias); } } } } $sql .= implode(",\n\t", $columns); } // from these joined tables if ($this->_parts[self::FROM]) { $from = array(); // array_pop() foreach ($this->_parts[self::FROM] as $correlationName => $table) { $tmp = ''; if (empty($from)) { // First table is named alone ignoring join information $tmp .= $this->_adapter->quoteTableAs($table['tableName'], $correlationName); } else { // Subsequent tables may have joins if (!empty($table['joinType'])) { $tmp .= ' ' . strtoupper($table['joinType']) . ' '; } $tmp .= $this->_adapter->quoteTableAs($table['tableName'], $correlationName); if (!empty($table['joinCondition'])) { $tmp .= ' ON ' . $table['joinCondition']; } } // add the table name and condition // add to the list $from[] = $tmp; } // add the list of all joins if (!empty($from)) { $sql .= "\nFROM " . implode("\n", $from); } // with these where conditions if ($this->_parts[self::WHERE]) { $sql .= "\nWHERE\n\t"; $sql .= implode("\n\t", $this->_parts[self::WHERE]); } // grouped by these columns if ($this->_parts[self::GROUP]) { $sql .= "\nGROUP BY\n\t"; $l = array(); foreach ($this->_parts[self::GROUP] as $term) { $l[] = $this->_adapter->quoteIdentifier($term); } $sql .= implode(",\n\t", $l); } // having these conditions if ($this->_parts[self::HAVING]) { $sql .= "\nHAVING\n\t"; $sql .= implode("\n\t", $this->_parts[self::HAVING]); } } // ordered by these columns if ($this->_parts[self::ORDER]) { $sql .= "\nORDER BY\n\t"; $l = array(); foreach ($this->_parts[self::ORDER] as $term) { if (is_array($term)) { $l[] = $this->_adapter->quoteIdentifier($term[0]) . ' ' . $term[1]; } else { $l[] = $this->_adapter->quoteIdentifier($term); } } $sql .= implode(",\n\t", $l); } // determine offset $count = 0; $offset = 0; if (!empty($this->_parts[self::LIMIT_OFFSET])) { $offset = (int) $this->_parts[self::LIMIT_OFFSET]; // this should be reduced to the max integer PHP can support $count = intval(9223372036854775807); } // determine count if (!empty($this->_parts[self::LIMIT_COUNT])) { $count = (int) $this->_parts[self::LIMIT_COUNT]; } // add limits clause if ($count > 0) { $sql .= "\n"; $sql = trim($this->_adapter->limit($sql, $count, $offset)); } return $sql; }
/** * Converts this object to an SQL SELECT string. * * @todo use $this->_adapter->quoteColumns() for non-PDO adapters * @todo use $this->_adapter->quoteTableNames() for non-PDO adapters * @todo use prepared queries for PDO adapters instead of constructing all the SQL ourselves * like in Adapter/Abstract.php.html:query() * @return string This object as a SELECT string. */ public function __toString() { // initial SELECT [DISTINCT] [FOR UPDATE] $sql = "SELECT"; if ($this->_parts['distinct']) { $sql .= " DISTINCT"; } if ($this->_parts['forUpdate']) { $sql .= " FOR UPDATE"; } $sql .= "\n\t"; // add columns if ($this->_parts['cols']) { $sql .= implode(",\n\t", $this->_parts['cols']) . "\n"; } // from these tables if ($this->_parts['from']) { $sql .= "FROM "; $sql .= implode(", ", array_keys($this->_parts['from'])) . "\n"; } // joined to these tables if ($this->_parts['join']) { $list = array(); foreach ($this->_parts['join'] as $join) { $tmp = ''; // add the type (LEFT, INNER, etc) if (!empty($join['type'])) { $tmp .= strtoupper($join['type']) . ' '; } // add the table name and condition $tmp .= 'JOIN ' . $join['name']; $tmp .= ' ON ' . $join['cond']; // add to the list $list[] = $tmp; } // add the list of all joins $sql .= implode("\n", $list) . "\n"; } // with these where conditions if ($this->_parts['where']) { $sql .= "WHERE\n\t"; $sql .= implode("\n\t", $this->_parts['where']) . "\n"; } // grouped by these columns if ($this->_parts['group']) { $sql .= "GROUP BY\n\t"; $sql .= implode(",\n\t", $this->_parts['group']) . "\n"; } // having these conditions if ($this->_parts['having']) { $sql .= "HAVING\n\t"; $sql .= implode("\n\t", $this->_parts['having']) . "\n"; } // ordered by these columns if ($this->_parts['order']) { $sql .= "ORDER BY\n\t"; $sql .= implode(",\n\t", $this->_parts['order']) . "\n"; } // determine count $count = !empty($this->_parts['limitCount']) ? (int) $this->_parts['limitCount'] : 0; // determine offset $offset = !empty($this->_parts['limitOffset']) ? (int) $this->_parts['limitOffset'] : 0; // add limits, and done return trim($this->_adapter->limit($sql, $count, $offset)); }