PHP Query::where方法代码示例

 public function findCursor(Query $query)
 {
     $current = $this->request->query('cursor');
     $limit = $this->request->query('limit') ?: 10;
     if ($current) {
         $query->where(['id >' => $current]);
     }
     $query->limit($limit);
     return $query;
 }

 /**
  * Tests that empty values don't set where clauses.
  *
  * @return void
  */
 public function testWhereEmptyValues()
 {
     $query = new Query($this->connection);
     $query->from('comments')->where('');
     $this->assertCount(0, $query->clause('where'));
     $query->where([]);
     $this->assertCount(0, $query->clause('where'));
 }

 public function findPublished(Query $query, array $options)
 {
     return $query->where(['published' => true]);
 }

 /**
  * 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_';
     $order = $original->clause('order') ?: new OrderByExpression('(SELECT NULL)');
     $query = clone $original;
     $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} > " . (int) $offset]);
     }
     if ($limit) {
         $value = (int) $offset + (int) $limit;
         $outer->where(["{$field} <= {$value}"]);
     }
     // 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;
 }

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

 protected function _addBooleanCondition(Query $query, $fieldName, $value, array $options = array())
 {
     if (in_array($value, [0, 1, true, false, 'true', 'false'])) {
         $query->where([$fieldName => $value]);
     }
 }

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