PHP Query::select方法代码示例

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

 /**
  * Shows that bufferResults(false) will prevent client-side results buffering
  *
  * @return void
  */
 public function testUnbufferedQuery()
 {
     $query = new Query($this->connection);
     $result = $query->select(['body', 'author_id'])->from('articles')->bufferResults(false)->execute();
     if (!method_exists($result, 'bufferResults')) {
         $result->closeCursor();
         $this->skipIf(true, 'This driver does not support unbuffered queries');
     }
     $this->assertCount(0, $result);
     $list = $result->fetchAll('assoc');
     $this->assertCount(3, $list);
     $result->closeCursor();
     $query = new Query($this->connection);
     $result = $query->select(['body', 'author_id'])->from('articles')->execute();
     $this->assertCount(3, $result);
     $list = $result->fetchAll('assoc');
     $this->assertCount(3, $list);
     $result->closeCursor();
 }

 /**
  * {@inheritDoc}
  *
  * If you pass an instance of a `Cake\ORM\Table` or `Cake\ORM\Association` class,
  * all the fields in the schema of the table or the association will be added to
  * the select clause.
  *
  * @param array|ExpressionInterface|string|\Cake\ORM\Table|\Cake\ORM\Association $fields fields
  * to be added to the list.
  * @param bool $overwrite whether to reset fields with passed list or not
  */
 public function select($fields = [], $overwrite = false)
 {
     if ($fields instanceof Association) {
         $fields = $fields->target();
     }
     if ($fields instanceof Table) {
         $fields = $this->aliasFields($fields->schema()->columns(), $fields->alias());
     }
     return parent::select($fields, $overwrite);
 }

 /**
  * Testing counter cache with lambda returning subqueryn
  *
  * @return void
  */
 public function testLambdaSubquery()
 {
     $this->post->belongsTo('Users');
     $this->post->addBehavior('CounterCache', ['Users' => ['posts_published' => function (Event $event, Entity $entity, Table $table) {
         $query = new Query($this->connection);
         return $query->select(4);
     }]]);
     $before = $this->_getUser();
     $entity = $this->_getEntity();
     $this->post->save($entity);
     $after = $this->_getUser();
     $this->assertEquals(1, $before->get('posts_published'));
     $this->assertEquals(4, $after->get('posts_published'));
 }

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

 /**
  * Generate a paging subquery for older versions of Oracle Server.
  *
  * Prior to Oracle 12 there was no equivalent to LIMIT OFFSET,
  * so a subquery must be used.
  *
  * @param \Cake\Database\Query $original The query to wrap in a subquery.
  * @param int $limit The number of rows to fetch.
  * @param int $offset The number of rows to offset.
  * @return \Cake\Database\Query Modified query object.
  */
 protected function _pagingSubquery($original, $limit, $offset)
 {
     $field = 'cake_paging_out."_cake_page_rownum_"';
     $query = clone $original;
     $query->limit(null)->offset(null);
     $outer = new Query($query->connection());
     $outer->select(['cake_paging.*', '_cake_page_rownum_' => new SimpleExpression('ROWNUM')])->from(['cake_paging' => $query]);
     $outer2 = new Query($query->connection());
     $outer2->select('*')->from(['cake_paging_out' => $outer]);
     if ($offset) {
         $outer2->where(["{$field} > " . (int) $offset]);
     }
     if ($limit) {
         $value = (int) $offset + (int) $limit;
         $outer2->where(["{$field} <= {$value}"]);
     }
     $original->decorateResults(function ($row) {
         if (isset($row['_cake_page_rownum_'])) {
             unset($row['_cake_page_rownum_']);
         }
         return $row;
     });
     return $outer2;
 }

 /**
  * Test select with limit on lte SQLServer2008
  *
  * @return void
  */
 public function testSelectLimitOldServer()
 {
     $driver = $this->getMock('Cake\\Database\\Driver\\Sqlserver', ['_connect', 'connection', '_version'], [[]]);
     $driver->expects($this->any())->method('_version')->will($this->returnValue(8));
     $connection = $this->getMock('\\Cake\\Database\\Connection', ['connect', 'driver'], [['log' => false]]);
     $connection->expects($this->any())->method('driver')->will($this->returnValue($driver));
     $query = new \Cake\Database\Query($connection);
     $query->select(['id', 'title'])->from('articles')->limit(10);
     $expected = 'SELECT TOP 10 id, title FROM articles';
     $this->assertEquals($expected, $query->sql());
     $query = new \Cake\Database\Query($connection);
     $query->select(['id', 'title'])->from('articles')->offset(10);
     $expected = 'SELECT * FROM (SELECT id, title, (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS [_cake_page_rownum_] ' . 'FROM articles) _cake_paging_ ' . 'WHERE _cake_paging_._cake_page_rownum_ > 10';
     $this->assertEquals($expected, $query->sql());
     $query = new \Cake\Database\Query($connection);
     $query->select(['id', 'title'])->from('articles')->order(['id'])->offset(10);
     $expected = 'SELECT * FROM (SELECT id, title, (ROW_NUMBER() OVER (ORDER BY id)) AS [_cake_page_rownum_] ' . 'FROM articles) _cake_paging_ ' . 'WHERE _cake_paging_._cake_page_rownum_ > 10';
     $this->assertEquals($expected, $query->sql());
     $query = new \Cake\Database\Query($connection);
     $query->select(['id', 'title'])->from('articles')->order(['id'])->where(['title' => 'Something'])->limit(10)->offset(50);
     $expected = 'SELECT * FROM (SELECT id, title, (ROW_NUMBER() OVER (ORDER BY id)) AS [_cake_page_rownum_] ' . 'FROM articles WHERE title = :c0) _cake_paging_ ' . 'WHERE (_cake_paging_._cake_page_rownum_ > 50 AND _cake_paging_._cake_page_rownum_ <= 60)';
     $this->assertEquals($expected, $query->sql());
 }

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

 /**
  * Tests that UNION ALL can be built by setting the second param of union() to true
  *
  * @return void
  */
 public function testUnionAll()
 {
     $union = (new Query($this->connection))->select(['id', 'title'])->from(['a' => 'articles']);
     $query = new Query($this->connection);
     $result = $query->select(['id', FunctionsBuilder::toChar(new IdentifierExpression('comment'))])->from(['c' => 'comments'])->union($union)->execute();
     $rows = $result->fetchAll();
     $this->assertCount(self::ARTICLE_COUNT + self::COMMENT_COUNT, $rows);
     $union->select(['foo' => 'id', 'bar' => 'title']);
     $union = (new Query($this->connection))->select(['id', 'name', 'other' => 'id', 'nameish' => 'name'])->from(['b' => 'authors'])->where(['id ' => 1]);
     $query->select(['foo' => 'id', 'bar' => FunctionsBuilder::toChar(new IdentifierExpression('comment'))])->unionAll($union);
     $result = $query->execute();
     $rows2 = $result->fetchAll();
     $this->assertCount(1 + self::COMMENT_COUNT + self::ARTICLE_COUNT, $rows2);
     $this->assertNotEquals($rows, $rows2);
 }

 /**
  * Generate a paging subquery for older versions of SQLserver.
  *
  * Prior to SQLServer 2012 there was no equivalent to LIMIT OFFSET, so a subquery must
  * be used.
  *
  * @param \Cake\Database\Query $original The query to wrap in a subquery.
  * @param int $limit The number of rows to fetch.
  * @param int $offset The number of rows to offset.
  * @return \Cake\Database\Query Modified query object.
  */
 protected function _pagingSubquery($original, $limit, $offset)
 {
     $field = '_cake_paging_._cake_page_rownum_';
     $query = clone $original;
     $order = $query->clause('order') ?: new OrderByExpression('NULL');
     $query->select(['_cake_page_rownum_' => new UnaryExpression('ROW_NUMBER() OVER', $order)])->limit(null)->offset(null)->order([], true);
     $outer = new Query($query->connection());
     $outer->select('*')->from(['_cake_paging_' => $query]);
     if ($offset) {
         $outer->where(["{$field} >" => $offset]);
     }
     if ($limit) {
         $outer->where(["{$field} <=" => (int) $offset + (int) $limit]);
     }
     // 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_page_rownum_'])) {
             unset($row['_cake_page_rownum_']);
         }
         return $row;
     });
     return $outer;
 }

 /**
  * Tests converting a query to a string
  *
  * @return void
  */
 public function testToString()
 {
     $query = new Query($this->connection);
     $query->select(['title'])->from('articles');
     $result = (string) $query;
     $this->assertQuotedQuery('SELECT <title> FROM <articles>', $result, true);
 }

 /**
  * Tests that case statements work correctly for various use-cases.
  *
  * @return void
  */
 public function testSqlCaseStatement()
 {
     $query = new Query($this->connection);
     $publishedCase = $query->newExpr()->addCase($query->newExpr()->add(['published' => 'Y']), 1, 'integer');
     $notPublishedCase = $query->newExpr()->addCase($query->newExpr()->add(['published' => 'N']), 1, 'integer');
     //Postgres requires the case statement to be cast to a integer
     if ($this->connection->driver() instanceof \Cake\Database\Driver\Postgres) {
         $publishedCase = $query->func()->cast([$publishedCase, 'integer' => 'literal'])->type(' AS ');
         $notPublishedCase = $query->func()->cast([$notPublishedCase, 'integer' => 'literal'])->type(' AS ');
     }
     $results = $query->select(['published' => $query->func()->sum($publishedCase), 'not_published' => $query->func()->sum($notPublishedCase)])->from(['comments'])->execute()->fetchAll('assoc');
     $this->assertEquals(5, $results[0]['published']);
     $this->assertEquals(1, $results[0]['not_published']);
     $query = new Query($this->connection);
     $query->insert(['article_id', 'user_id', 'comment', 'published'])->into('comments')->values(['article_id' => 2, 'user_id' => 1, 'comment' => 'In limbo', 'published' => 'L'])->execute();
     $query = new Query($this->connection);
     $conditions = [$query->newExpr()->add(['published' => 'Y']), $query->newExpr()->add(['published' => 'N'])];
     $values = ['Published', 'Not published', 'None'];
     $results = $query->select(['id', 'comment', 'status' => $query->newExpr()->addCase($conditions, $values)])->from(['comments'])->execute()->fetchAll('assoc');
     $this->assertEquals('Published', $results[2]['status']);
     $this->assertEquals('Not published', $results[3]['status']);
     $this->assertEquals('None', $results[6]['status']);
 }

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

 /**
  * Tests that types in the type map are used in the
  * specific comparison functions when using a callable
  *
  * @return void
  */
 public function testBetweenExpressionAndTypeMap()
 {
     $query = new Query($this->connection);
     $query->select('id')->from('comments')->defaultTypes(['created' => 'datetime'])->where(function ($expr) {
         $from = new \DateTime('2007-03-18 10:45:00');
         $to = new \DateTime('2007-03-18 10:48:00');
         return $expr->between('created', $from, $to);
     });
     $this->assertCount(2, $query->execute()->fetchAll());
 }

 /**
  * Test removeJoin().
  *
  * @return void
  */
 public function testRemoveJoin()
 {
     $query = new Query($this->connection);
     $query->select(['id', 'title'])->from('articles')->join(['authors' => ['type' => 'INNER', 'conditions' => ['articles.author_id = authors.id']]]);
     $this->assertArrayHasKey('authors', $query->join());
     $this->assertSame($query, $query->removeJoin('authors'));
     $this->assertArrayNotHasKey('authors', $query->join());
 }