PHP Database\Query類代碼示例

 public function findProductId(Query $query, array $options)
 {
     $result = $query->select(['id'])->where(['article_uid' => $options['uid']])->first();
     if ($result == null) {
         return null;
     } else {
         return $result->id;
     }
 }

 public function findCursor(Query $query)
 {
     $current = $this->request->query('cursor');
     $limit = $this->request->query('limit') ?: 10;
     if ($current) {
         $query->where(['id >' => $current]);
     }
     $query->limit($limit);
     return $query;
 }

 /**
  * Conditionally adds a condition to the passed Query that will make it find
  * records where there is no match with this association.
  *
  * @param \Cake\Database\Query $query The query to modify
  * @param array $options Options array containing the `negateMatch` key.
  * @return void
  */
 protected function _appendNotMatching($query, $options)
 {
     $target = $this->_targetTable;
     if (!empty($options['negateMatch'])) {
         $primaryKey = $query->aliasFields((array) $target->primaryKey(), $this->_name);
         $query->andWhere(function ($exp) use($primaryKey) {
             array_map([$exp, 'isNull'], $primaryKey);
             return $exp;
         });
     }
 }

 /**
  * Returns the SQL representation of the provided query after generating
  * the placeholders for the bound values using the provided generator
  *
  * @param \Cake\Database\Query $query The query that is being compiled
  * @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions
  * @return \Closure
  */
 public function compile($query, $generator)
 {
     $sql = '';
     $type = $query->type();
     $query->traverse($this->_sqlCompiler($sql, $query, $generator), $this->{'_' . $type . 'Parts'});
     return $sql;
 }

 /**
  * @param array $parts
  * @param \Cake\Database\Query $query
  * @param \Cake\Database\ValueBinder $generator
  * @return string
  */
 protected function _buildSelectPart($parts, $query, $generator)
 {
     $driver = $query->connection()->driver();
     $select = 'SELECT %s%s%s';
     if ($this->_orderedUnion && $query->clause('union')) {
         $select = '(SELECT %s%s%s';
     }
     $distinct = $query->clause('distinct');
     $modifiers = $query->clause('modifier') ?: null;
     $normalized = [];
     $parts = $this->_stringifyExpressions($parts, $generator);
     foreach ($parts as $k => $p) {
         if (!is_numeric($k)) {
             $p = $p . ' AS "' . $k . '"';
         }
         $normalized[] = $p;
     }
     if ($distinct === true) {
         $distinct = 'DISTINCT ';
     }
     if (is_array($distinct)) {
         $distinct = $this->_stringifyExpressions($distinct, $generator);
         $distinct = sprintf('DISTINCT ON (%s) ', implode(', ', $distinct));
     }
     if ($modifiers !== null) {
         $modifiers = $this->_stringifyExpressions($modifiers, $generator);
         $modifiers = implode(' ', $modifiers) . ' ';
     }
     return sprintf($select, $distinct, $modifiers, implode(', ', $normalized));
 }

 /**
  * Transforms an insert query that is meant to insert multiple rows at a time,
  * otherwise it leaves the query untouched.
  *
  * The way Firebird works with multi insert is by having multiple select statements
  * joined with UNION.
  *
  * @param \Cake\Database\Query $query The query to translate
  * @return \Cake\Database\Query
  */
 protected function _insertQueryTranslator($query)
 {
     $v = $query->clause('values');
     if (count($v->values()) === 1 || $v->query()) {
         return $query;
     }
     $newQuery = $query->connection()->newQuery();
     $cols = $v->columns();
     $placeholder = 0;
     $replaceQuery = false;
     foreach ($v->values() as $k => $val) {
         $fillLength = count($cols) - count($val);
         if ($fillLength > 0) {
             $val = array_merge($val, array_fill(0, $fillLength, null));
         }
         foreach ($val as $col => $attr) {
             if (!$attr instanceof ExpressionInterface) {
                 $val[$col] = sprintf(':c%d', $placeholder);
                 $placeholder++;
             }
         }
         $select = array_combine($cols, $val);
         if ($k === 0) {
             $replaceQuery = true;
             $newQuery->select($select);
             continue;
         }
         $q = $newQuery->connection()->newQuery();
         $newQuery->unionAll($q->select($select));
     }
     if ($replaceQuery) {
         $v->query($newQuery);
     }
     return $query;
 }

 /**
  * Builds the SQL fragment for INSERT INTO.
  *
  * @param array $parts The insert parts.
  * @param \Cake\Database\Query $query The query that is being compiled
  * @param \Cake\Database\ValueBinder $generator the placeholder generator to be used in expressions
  * @return string SQL fragment.
  */
 protected function _buildInsertPart($parts, $query, $generator)
 {
     $driver = $query->connection()->driver();
     $table = $driver->quoteIfAutoQuote($parts[0]);
     $columns = $this->_stringifyExpressions($parts[1], $generator);
     return sprintf('INSERT INTO %s (%s)', $table, implode(', ', $columns));
 }

 /**
  * Prepares a sql statement to be executed
  *
  * @param string|\Cake\Database\Query $query The query to prepare.
  * @return \Cake\Database\StatementInterface
  */
 public function prepare($query)
 {
     $this->connect();
     $options = [PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL];
     $isObject = $query instanceof Query;
     if ($isObject && $query->bufferResults() === false) {
         $options = [];
     }
     $statement = $this->_connection->prepare($isObject ? $query->sql() : $query, $options);
     return new SqlserverStatement($statement, $this);
 }

 /**
  * Modify the limit/offset to TSQL
  *
  * @param Cake\Database\Query $query The query to translate
  * @return Cake\Database\Query The modified query
  */
 protected function _selectQueryTranslator($query)
 {
     $limit = $query->clause('limit');
     $offset = $query->clause('offset');
     if ($limit && $offset === null) {
         $query->modifier(['_auto_top_' => sprintf('TOP %d', $limit)]);
     }
     if ($offset !== null && !$query->clause('order')) {
         $query->order($query->newExpr()->add('SELECT NULL'));
     }
     if ($this->_version() < 11 && $offset !== null) {
         return $this->_pagingSubquery($query, $limit, $offset);
     }
     return $query;
 }

 /**
  * Prepares a sql statement to be executed
  *
  * @param string|\Cake\Database\Query $query The query to turn into a prepared statement.
  * @return \Cake\Database\StatementInterface
  */
 public function prepare($query)
 {
     $this->connect();
     $isObject = $query instanceof Query;
     $statement = $this->_connection->prepare($isObject ? $query->sql() : $query);
     return new PDOStatement($statement, $this);
 }

 /**
  * Modifies the original insert query to append a "RETURNING *" epilogue
  * so that the latest insert id can be retrieved
  *
  * @param \Cake\Database\Query $query The query to translate.
  * @return \Cake\Database\Query
  */
 protected function _insertQueryTranslator($query)
 {
     if (!$query->clause('epilog')) {
         $query->epilog('RETURNING *');
     }
     return $query;
 }

 /**
  * Prepares a sql statement to be executed
  *
  * @param string|\Cake\Database\Query $query The query to prepare.
  * @return \Cake\Database\StatementInterface
  */
 public function prepare($query)
 {
     $this->connect();
     $isObject = $query instanceof Query;
     $statement = $this->_connection->prepare($isObject ? $query->sql() : $query);
     $result = new SqliteStatement(new PDOStatement($statement, $this), $this);
     if ($isObject && $query->bufferResults() === false) {
         $result->bufferResults(false);
     }
     return $result;
 }

 /**
  * Prepares a sql statement to be executed
  *
  * @param string|\Cake\Database\Query $query The query to prepare.
  * @return \Cake\Database\StatementInterface
  */
 public function prepare($query)
 {
     $this->connect();
     $options = [PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY];
     $isObject = $query instanceof Query;
     if ($isObject && $query->bufferResults() === false) {
         $options = [];
     }
     $statement = $this->_connection->prepare($isObject ? $query->sql() : $query, $options);
     $result = new CustomSqlserverStatement(new SqlserverStatement($statement, $this), $this);
     if ($isObject && $query->bufferResults() === false) {
         $result->bufferResults(false);
     }
     return $result;
 }

 /**
  * Generates the LIMIT part of a SQL query
  *
  * @param int $limit the limit clause
  * @param \Cake\Database\Query $query The query that is being compiled
  * @return string
  */
 protected function _buildLimitPart($limit, $query)
 {
     if ($limit === null || $query->clause('offset') === null) {
         return '';
     }
     return sprintf(' FETCH FIRST %d ROWS ONLY', $limit);
 }

 /**
  * Receives a TupleExpression and changes it so that it conforms to this
  * SQL dialect.
  *
  * It transforms expressions looking like '(a, b) IN ((c, d), (e, f)' into an
  * equivalent expression of the form '((a = c) AND (b = d)) OR ((a = e) AND (b = f))'.
  *
  * It can also transform transform expressions where the right hand side is a query
  * selecting the same amount of columns as the elements in the left hand side of
  * the expression:
  *
  * (a, b) IN (SELECT c, d FROM a_table) is transformed into
  *
  * 1 = (SELECT 1 FROM a_table WHERE (a = c) AND (b = d))
  *
  * @param \Cake\Database\Expression\TupleComparison $expression The expression to transform
  * @param \Cake\Database\Query $query The query to update.
  * @return void
  */
 protected function _transformTupleComparison(TupleComparison $expression, $query)
 {
     $fields = $expression->getField();
     if (!is_array($fields)) {
         return;
     }
     $value = $expression->getValue();
     $op = $expression->getOperator();
     $true = new QueryExpression('1');
     if ($value instanceof Query) {
         $selected = array_values($value->clause('select'));
         foreach ($fields as $i => $field) {
             $value->andWhere([$field . " {$op}" => new IdentifierExpression($selected[$i])]);
         }
         $value->select($true, true);
         $expression->setField($true);
         $expression->setOperator('=');
         return;
     }
     $surrogate = $query->connection()->newQuery()->select($true);
     if (!is_array(current($value))) {
         $value = [$value];
     }
     foreach ($value as $tuple) {
         $surrogate->orWhere(function ($exp) use($fields, $tuple) {
             foreach (array_values($tuple) as $i => $value) {
                 $exp->add([$fields[$i] => $value]);
             }
             return $exp;
         });
     }
     $expression->setField($true);
     $expression->setValue($surrogate);
     $expression->setOperator('=');
 }