PHP Query::order方法代码示例

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