Fields can be passed as an array of strings, array of expression
objects, a single expression or a single string.
If an array is passed, keys will be used as the field itself and the value will
represent the order in which such field should be ordered. When called multiple
times with the same fields as key, the last order definition will prevail over
the others.
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->order(['title' => 'DESC', 'author_id' => 'ASC']);
Produces:
ORDER BY title DESC, author_id ASC
$query->order(['title' => 'DESC NULLS FIRST'])->order('author_id');
Will generate:
ORDER BY title DESC NULLS FIRST, author_id
$expression = $query->newExpr()->add(['id % 2 = 0']);
$query->order($expression)->order(['title' => 'ASC']);
Will become:
ORDER BY (id %2 = 0), title ASC
If you need to set complex expressions as order conditions, you
should use orderAsc() or orderDesc().
/**
* @inheritDoc
*/
public function testSelectOrderBy()
{
$query = new Query($this->connection);
$result = $query->select(['id'])->from('articles')->order(['id' => 'desc'])->execute();
$this->assertEquals(['id' => 3], $result->fetch('assoc'));
$this->assertEquals(['id' => 2], $result->fetch('assoc'));
$this->assertEquals(['id' => 1], $result->fetch('assoc'));
$result = $query->order(['id' => 'asc'])->execute();
$this->assertEquals(['id' => 1], $result->fetch('assoc'));
$this->assertEquals(['id' => 2], $result->fetch('assoc'));
$this->assertEquals(['id' => 3], $result->fetch('assoc'));
$result = $query->order(['title' => 'asc'])->execute();
$this->assertEquals(['id' => 1], $result->fetch('assoc'));
$this->assertEquals(['id' => 2], $result->fetch('assoc'));
$this->assertEquals(['id' => 3], $result->fetch('assoc'));
$result = $query->order(['title' => 'asc'], true)->execute();
$this->assertEquals(['id' => 1], $result->fetch('assoc'));
$this->assertEquals(['id' => 2], $result->fetch('assoc'));
$this->assertEquals(['id' => 3], $result->fetch('assoc'));
$result = $query->order(['title' => 'asc', 'published' => 'asc'], true)->execute();
$this->assertEquals(['id' => 1], $result->fetch('assoc'));
$this->assertEquals(['id' => 2], $result->fetch('assoc'));
$this->assertEquals(['id' => 3], $result->fetch('assoc'));
$driver = $query->connection()->driver();
$idField = $driver->quoteIfAutoQuote('id');
$expression = $query->newExpr(["MOD(({$idField} + :offset), 2)"]);
$result = $query->order([$expression, 'id' => 'desc'], true)->bind(':offset', 1, null)->execute();
$this->assertEquals(['id' => 3], $result->fetch('assoc'));
$this->assertEquals(['id' => 1], $result->fetch('assoc'));
$this->assertEquals(['id' => 2], $result->fetch('assoc'));
$result = $query->order($expression, true)->order(['id' => 'asc'])->bind(':offset', 1, null)->execute();
$this->assertEquals(['id' => 1], $result->fetch('assoc'));
$this->assertEquals(['id' => 3], $result->fetch('assoc'));
$this->assertEquals(['id' => 2], $result->fetch('assoc'));
}
/**
* 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 $this->_transformDistinct($query);
}
/**
* Tests order() method both with simple fields and expressions
*
* @return void
*/
public function testSelectOrderBy()
{
$query = new Query($this->connection);
$result = $query->select(['id'])->from('articles')->order(['id' => 'desc'])->execute();
$this->assertEquals(['id' => 3], $result->fetch('assoc'));
$this->assertEquals(['id' => 2], $result->fetch('assoc'));
$this->assertEquals(['id' => 1], $result->fetch('assoc'));
$result = $query->order(['id' => 'asc'])->execute();
$this->assertEquals(['id' => 1], $result->fetch('assoc'));
$this->assertEquals(['id' => 2], $result->fetch('assoc'));
$this->assertEquals(['id' => 3], $result->fetch('assoc'));
$result = $query->order(['title' => 'asc'])->execute();
$this->assertEquals(['id' => 1], $result->fetch('assoc'));
$this->assertEquals(['id' => 2], $result->fetch('assoc'));
$this->assertEquals(['id' => 3], $result->fetch('assoc'));
$result = $query->order(['title' => 'asc'], true)->execute();
$this->assertEquals(['id' => 1], $result->fetch('assoc'));
$this->assertEquals(['id' => 2], $result->fetch('assoc'));
$this->assertEquals(['id' => 3], $result->fetch('assoc'));
$result = $query->order(['title' => 'asc', 'published' => 'asc'], true)->execute();
$this->assertEquals(['id' => 1], $result->fetch('assoc'));
$this->assertEquals(['id' => 2], $result->fetch('assoc'));
$this->assertEquals(['id' => 3], $result->fetch('assoc'));
$expression = $query->newExpr(['(id + :offset) % 2']);
$result = $query->order([$expression, 'id' => 'desc'], true)->bind(':offset', 1, null)->execute();
$this->assertEquals(['id' => 3], $result->fetch('assoc'));
$this->assertEquals(['id' => 1], $result->fetch('assoc'));
$this->assertEquals(['id' => 2], $result->fetch('assoc'));
$result = $query->order($expression, true)->order(['id' => 'asc'])->bind(':offset', 1, null)->execute();
$this->assertEquals(['id' => 1], $result->fetch('assoc'));
$this->assertEquals(['id' => 3], $result->fetch('assoc'));
$this->assertEquals(['id' => 2], $result->fetch('assoc'));
}
/**
* 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'));
}
/**
* 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);
}
}
