PHP Query::clause方法代码示例

 /**
  * 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;
 }

 /**
  * 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;
 }

 /**
  * Helper function used to build the string representation of a SELECT clause,
  * it constructs the field list taking care of aliasing and
  * converting expression objects to string. This function also constructs the
  * DISTINCT clause for the query. This has been modified to allow it to support Salesforce
  *
  * @param array $parts list of fields to be transformed to string
  * @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
  */
 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;
             //Leave it alone
         }
         $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));
 }

 /**
  * 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;
 }

 /**
  * 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);
 }

 /**
  * Test selecting rows using the page() method.
  *
  * @return void
  */
 public function testSelectPage()
 {
     $query = new Query($this->connection);
     $result = $query->select('id')->from('comments')->limit(1)->page(1)->execute();
     $this->assertCount(1, $result);
     $this->assertEquals(['id' => 1], $result->fetch('assoc'));
     $query = new Query($this->connection);
     $result = $query->select('id')->from('comments')->limit(1)->page(2)->order(['id' => 'asc'])->execute();
     $this->assertCount(1, $result);
     $this->assertEquals(['id' => 2], $result->fetch('assoc'));
     $query = new Query($this->connection);
     $query->select('id')->from('comments')->page(3, 10);
     $this->assertEquals(10, $query->clause('limit'));
     $this->assertEquals(20, $query->clause('offset'));
     $query = new Query($this->connection);
     $query->select('id')->from('comments')->page(1);
     $this->assertEquals(25, $query->clause('limit'));
     $this->assertEquals(0, $query->clause('offset'));
     $query->select('id')->from('comments')->page(2);
     $this->assertEquals(25, $query->clause('limit'));
     $this->assertEquals(25, $query->clause('offset'));
 }

 /**
  * Apply translation steps to delete queries.
  *
  * Chops out aliases on delete query conditions as most database dialects do not
  * support aliases in delete queries. This also removes aliases
  * in table names as they frequently don't work either.
  *
  * We are intentionally not supporting deletes with joins as they have even poorer support.
  *
  * @param \Cake\Database\Query $query The query to translate
  * @return \Cake\Database\Query The modified query
  */
 protected function _deleteQueryTranslator($query)
 {
     $hadAlias = false;
     $tables = [];
     foreach ($query->clause('from') as $alias => $table) {
         if (is_string($alias)) {
             $hadAlias = true;
         }
         $tables[] = $table;
     }
     if ($hadAlias) {
         $query->from($tables, true);
     }
     if (!$hadAlias) {
         return $query;
     }
     $conditions = $query->clause('where');
     if ($conditions) {
         $conditions->traverse(function ($condition) {
             if (!$condition instanceof Comparison) {
                 return $condition;
             }
             $field = $condition->getField();
             if ($field instanceof ExpressionInterface || strpos($field, '.') === false) {
                 return $condition;
             }
             list(, $field) = explode('.', $field);
             $condition->setField($field);
             return $condition;
         });
     }
     return $query;
 }

 /**
  * Quotes the table name and columns for an insert query
  *
  * @param Query $query
  * @return void
  */
 protected function _quoteInsert($query)
 {
     list($table, $columns) = $query->clause('insert');
     $table = $this->_driver->quoteIdentifier($table);
     foreach ($columns as &$column) {
         if (is_string($column)) {
             $column = $this->_driver->quoteIdentifier($column);
         }
     }
     $query->insert($columns)->into($table);
 }

 /**
  * Returns the passed query after rewriting the DISTINCT clause, so that drivers
  * that do not support the "ON" part can provide the actual way it should be done
  *
  * @param \Cake\Database\Query $original The query to be transformed
  * @return \Cake\Database\Query
  */
 protected function _transformDistinct($original)
 {
     if (!is_array($original->clause('distinct'))) {
         return $original;
     }
     $query = clone $original;
     $distinct = $query->clause('distinct');
     $query->distinct(false);
     $order = new OrderByExpression($distinct);
     $query->select(function ($q) use($distinct, $order) {
         $over = $q->newExpr('ROW_NUMBER() OVER')->add('(PARTITION BY')->add($q->newExpr()->add($distinct)->tieWith(','))->add($order)->add(')')->tieWith(' ');
         return ['_cake_distinct_pivot_' => $over];
     })->limit(null)->offset(null)->order([], true);
     $outer = new Query($query->connection());
     $outer->select('*')->from(['_cake_distinct_' => $query])->where(['_cake_distinct_pivot_' => 1]);
     // Decorate the original query as that is what the
     // end developer will be calling execute() on originally.
     $original->decorateResults(function ($row) {
         if (isset($row['_cake_distinct_pivot_'])) {
             unset($row['_cake_distinct_pivot_']);
         }
         return $row;
     });
     return $outer;
 }

 /**
  * Test that cloning goes deep.
  *
  * @return void
  */
 public function testDeepClone()
 {
     $query = new Query($this->connection);
     $query->select(['id', 'title' => $query->func()->concat(['title' => 'literal', 'test'])])->from('articles')->where(['Articles.id' => 1])->offset(10)->limit(1)->order(['Articles.id' => 'DESC']);
     $dupe = clone $query;
     $this->assertEquals($query->clause('where'), $dupe->clause('where'));
     $this->assertNotSame($query->clause('where'), $dupe->clause('where'));
     $dupe->where(['Articles.title' => 'thinger']);
     $this->assertNotEquals($query->clause('where'), $dupe->clause('where'));
     $this->assertNotSame($query->clause('select')['title'], $dupe->clause('select')['title']);
     $this->assertEquals($query->clause('order'), $dupe->clause('order'));
     $this->assertNotSame($query->clause('order'), $dupe->clause('order'));
     $query->order(['Articles.title' => 'ASC']);
     $this->assertNotEquals($query->clause('order'), $dupe->clause('order'));
 }

 /**
  * Quotes the table name for an update query
  *
  * @param \Cake\Database\Query $query The update query to quote.
  * @return void
  */
 protected function _quoteUpdate($query)
 {
     $table = $query->clause('update')[0];
     if (is_string($table)) {
         $query->update($this->_driver->quoteIdentifier($table));
     }
 }

 /**
  * Generates the EPILOG part of a SQL query, including special handling
  * if we added offset and/or limit wrappers earlier
  *
  * @param mixed $epilog the epilog clause
  * @param \Cake\Database\Query $query The query that is being compiled
  * @param \Cake\Database\ValueBinder $generator The placeholder and value binder object
  * @return string
  */
 protected function _buildEpilogPart($epilog, $query, $generator)
 {
     if (!is_array($epilog) || !array_key_exists('snelgOracleOrigEpilog', $epilog)) {
         $origEpilog = $epilog;
     } else {
         //We wrapped the original epilog, which might have been an
         //ExpressionInterface instead of a simple string
         $origEpilog = $epilog['snelgOracleOrigEpilog'];
     }
     //Duplicate original _sqlCompiler functionality...
     if ($origEpilog instanceof ExpressionInterface) {
         $origEpilog = [$origEpilog->sql($generator)];
     }
     $origEpilog = $this->_stringifyExpressions((array) $origEpilog, $generator);
     $epilogSql = sprintf(' %s', implode(', ', $origEpilog));
     //...and then add our own wrappers.
     /*
      * We need to double-check that "limit" and/or "offset"
      * are actually set because calls to $query->count() (e.g. in Paginator
      * component) reset the Query "limit" and "offset" to null
      */
     if (is_array($epilog)) {
         if (!empty($epilog['snelgOracleLimitEndWrap']) && intval($query->clause('limit') > 0)) {
             $epilogSql .= $epilog['snelgOracleLimitEndWrap'];
         }
         if (!empty($epilog['snelgOracleOffsetEndWrap']) && intval($query->clause('offset') > 0)) {
             $epilogSql .= $epilog['snelgOracleOffsetEndWrap'];
         }
     }
     return $epilogSql;
 }

 /**
  * Transforms an insert query that is meant to insert multiple rows at a time,
  * otherwise it leaves the query untouched.
  *
  * The way SQLite works with multi insert is by having multiple select statements
  * joined with UNION.
  *
  * @param \Cake\Database\Query $query The query to translate
  * @return 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();
     foreach ($v->values() as $k => $val) {
         $fillLength = count($cols) - count($val);
         if ($fillLength > 0) {
             $val = array_merge($val, array_fill(0, $fillLength, null));
         }
         $val = array_map(function ($val) {
             return $val instanceof ExpressionInterface ? $val : '?';
         }, $val);
         $select = array_combine($cols, $val);
         if ($k === 0) {
             $newQuery->select($select);
             continue;
         }
         $q = $newQuery->connection()->newQuery();
         $newQuery->unionAll($q->select($select));
     }
     if ($newQuery->type()) {
         $v->query($newQuery);
     }
     return $query;
 }

 /**
  * Available options are:
  * 
  * - for: The id of the record to read.
  * - direct: Boolean, whether to return only the direct (true), or all (false) children, 
  *           defaults to false (all children).
  * - order : The order to apply on found nodes. Default on 'model_sort_fieldname' config
  *               
  * If the direct option is set to true, only the direct children are returned (based upon the parent_id field)
  * 
  * @param \Cake\ORM\Query $query
  * @param array $options Array of options as described above
  * @return \Cake\ORM\Query
  */
 public function findChildren(Query $query, array $options)
 {
     $default_options = ['direct' => false, 'sort' => []];
     $options = array_merge($default_options, $options);
     $for = isset($options['for']) ? $options['for'] : null;
     if (empty($for)) {
         throw new \InvalidArgumentException("The 'for' key is required for find('children')");
     }
     if ($options['direct']) {
         /*
          * Add order clause if not already set
          */
         if ($query->clause('order') === null) {
             $sort = !empty($options['sort']) ? $options['sort'] : [$this->config('model_sort_fieldname') => 'asc'];
             $query->order($sort);
         }
         $query->where([$this->config('model_parent_id_fieldname') => $for]);
         return $query;
     } else {
         /*
          SELECT nodes.*, t2.max_level as level
         FROM nodes
         INNER JOIN
         (
         		SELECT nac.node_id, MAX(level) as max_level
         		FROM nodes_ancestors nac
         		INNER JOIN
         		(
         				SELECT node_id
         				FROM nodes_ancestors
         				WHERE ancestor_id = 1
         		) t ON t.node_id = nac.node_id
         		GROUP BY node_id
         ) t2 ON nodes.id = t2.node_id
         ORDER BY max_level ASC, sort ASC
         */
         $ancestorTable = $this->getAncestorTable($this->_table);
         $subquery2 = $ancestorTable->find()->select(['nac_node_id' => 'node_id'])->where(['ancestor_id' => $for]);
         $subquery1 = $ancestorTable->find()->select(['node_id' => 'nac_node_id', 'max_level' => $subquery2->func()->max('level')])->join(['table' => $subquery2, 'alias' => 't', 'type' => 'INNER', 'conditions' => 't.nac_node_id = Ancestors.node_id'])->group(['node_id']);
         $selected_fields = $this->_table->schema()->columns();
         $selected_fields['level'] = 't2.max_level';
         $query->select($selected_fields)->join(['table' => $subquery1, 'alias' => 't2', 'type' => 'INNER', 'conditions' => $this->_table->alias() . '.id = t2.node_id'])->order(['max_level' => 'ASC', 'sort' => 'ASC']);
         return $query;
         // 			/*
         // 			SELECT n2.*
         // 			FROM nodes n1
         // 			INNER JOIN nodes_ancestors ON ancestor_id = n1.id
         // 			INNER JOIN nodes n2 ON node_id = n2.id
         // 			WHERE ancestor_id = 1
         // 			ORDER BY level ASC, n1.sort ASC
         // 			*/
         // 			/*
         // 			 * 1) Find all nodes linked to the ancestors that are under the searched item
         // 			 * 2) Create a new collection based on the items as we don't want a Collection of ancestors
         // 			 * 3) if $options['multilevel'] is true -> organize items as a multilevel array
         // 			 */
         // 			$ancestor_table = $this->getAncestorTable($this->_table);
         // 			$model_alias = $this->_table->alias();
         // 			$ancestor_table->belongsTo($model_alias, [
         // 					'className'    => $model_alias,
         // 					'foreignKey'   => 'node_id',
         // 					'propertyName' => 'linked_node'
         // 				]);
         // 			$order = [];
         // 			$order['level'] = 'ASC';
         // 			if(isset($options['sort']))
         // 			{
         // 				$order = $order + $options['sort'];
         // 			}
         // 			$query = $ancestor_table->find();
         // 			$query->contain([$model_alias]);
         // 			$query->order($order);
         // 			$query->where(['ancestor_id' => $for]);
         // 			$nodes = [];
         // 			foreach($query as $ancestor_entity){
         // 				$nodes[] = $ancestor_entity->linked_node;
         // 			}
         // 			return new \Cake\Collection\Collection($nodes);
     }
 }