If an array is passed, keys will be used to alias fields using the value as the
real field to be aliased. It is possible to alias strings, Expression objects or
even other Query objects.
If a callable function is passed, the returning array of the function will
be used as the list of fields.
By default this function will append any passed argument to the list of fields
to be selected, unless the second argument is set to true.
### Examples:
$query->select(['id', 'title']); // Produces SELECT id, title
$query->select(['author' => 'author_id']); // Appends author: SELECT id, title, author_id as author
$query->select('id', true); // Resets the list: SELECT id
$query->select(['total' => $countQuery]); // SELECT id, (SELECT ...) AS total
$query->select(function ($query) {
return ['article_id', 'total' => $query->count('*')];
})
By default no fields are selected, if you have an instance of Cake\ORM\Query and try to append
fields you should also call Cake\ORM\Query::autoFields() to select the default fields
from the table.
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());
}
/**
* Tests that fetch returns an anonymous object when the string 'obj'
* is passed as an argument
*
* @return void
*/
public function testSelectWithObjFetchType()
{
$this->loadFixtures('Comments');
$query = new Query($this->connection);
$result = $query->select(['id'])->from('comments')->where(['id' => '1'])->execute();
$obj = (object) ['id' => 1];
$this->assertEquals($obj, $result->fetch('obj'));
$query = new Query($this->connection);
$result = $query->select(['id'])->from('comments')->where(['id' => '1'])->execute();
$rows = $result->fetchAll('obj');
$this->assertEquals($obj, $rows[0]);
}