/**
* 查询记录
*
* @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));
}
