PHP ORM\Query类代码示例

 public function _addParametersToQuery(Query $query, $restrictTo = [])
 {
     $parameters = $this->request->query;
     $parameters = $this->_restrictTo($parameters, $restrictTo);
     $query->where($parameters);
     return $query;
 }

 public function init()
 {
     parent::init();
     // 1. Not all fixtures want to use the dev db.
     if (is_null($this->tableName)) {
         return;
     }
     // 2. We need to do this to ensure that the tables really do use the connection to the
     // dev db.
     TableRegistry::remove($this->tableName);
     $table = TableRegistry::get($this->tableName, ['connection' => ConnectionManager::get('dev')]);
     //if(!is_null($this->joinTableName)) {
     //TableRegistry::remove($this->joinTableName);
     //$n=TableRegistry::get($this->joinTableName, ['connection' => ConnectionManager::get('fixture')]);
     //}
     // 3. Now build the query to retrieve the source records
     $query = new Query(ConnectionManager::get('dev'), $table);
     $query->find('all');
     //if(!is_null($this->order)) $query->order($this->order);
     //if(!is_null($this->joinTableName)) $query->leftJoin($this->joinTableName,'semesters.id = sections.semester_id');
     //$c=$query->count();
     // 4. Copy the records
     /* @var \Cake\ORM\Entity $record */
     foreach ($query as $record) {
         $this->records[] = $record->toArray();
     }
     // 5. Do this again to ensure that the table uses the 'test' connection.
     TableRegistry::remove($this->tableName);
     TableRegistry::get($this->tableName, ['connection' => ConnectionManager::get('test')]);
     //if(!is_null($this->joinTableName)) {
     //TableRegistry::remove($this->joinTableName);
     //TableRegistry::get($this->joinTableName, ['connection' => ConnectionManager::get('test')]);
     //}
 }

 /**
  * Gets a list of all virtual columns present in given $query's SELECT clause.
  *
  * This method will alter the given Query object removing any virtual column
  * present in its SELECT clause in order to avoid incorrect SQL statements.
  * Selected virtual columns should be fetched after query is executed using
  * mapReduce or similar.
  *
  * @param \Cake\ORM\Query $query The query object to be scoped
  * @param string|null $bundle Consider attributes only for a specific bundle
  * @return array List of virtual columns names
  */
 public function getVirtualColumns(Query $query, $bundle = null)
 {
     static $selectedVirtual = [];
     $cacheKey = md5($query->sql()) . '_' . $bundle;
     if (isset($selectedVirtual[$cacheKey])) {
         return $selectedVirtual[$cacheKey];
     }
     $selectClause = (array) $query->clause('select');
     if (empty($selectClause)) {
         $selectedVirtual[$cacheKey] = array_keys($this->_toolbox->attributes($bundle));
         return $selectedVirtual[$cacheKey];
     }
     $selectedVirtual[$cacheKey] = [];
     $virtualColumns = array_keys($this->_toolbox->attributes($bundle));
     foreach ($selectClause as $index => $column) {
         list($table, $column) = pluginSplit($column);
         if ((empty($table) || $table == $this->_table->alias()) && in_array($column, $virtualColumns)) {
             $selectedVirtual[$cacheKey][$index] = $column;
             unset($selectClause[$index]);
         }
     }
     if (empty($selectClause) && !empty($selectedVirtual[$cacheKey])) {
         $selectClause[] = $this->_table->primaryKey();
     }
     $query->select($selectClause, true);
     return $selectedVirtual[$cacheKey];
 }

 /**
  * Custom finder for map the ntofications.
  *
  * @param \Cake\ORM\Query $query The query finder.
  * @param array $options The options passed in the query builder.
  *
  * @return \Cake\ORM\Query
  */
 public function findMap(Query $query, array $options)
 {
     return $query->formatResults(function ($notifications) use($options) {
         return $notifications->map(function ($notification) use($options) {
             $notification->data = unserialize($notification->data);
             switch ($notification->type) {
                 case 'conversation.reply':
                     $username = $notification->data['sender']->username;
                     $conversationTitle = Text::truncate($notification->data['conversation']->title, 50, ['ellipsis' => '...', 'exact' => false]);
                     //Check if the creator of the conversation is the current user.
                     if ($notification->data['conversation']->user_id === $options['session']->read('Auth.User.id')) {
                         $notification->text = __('<strong>{0}</strong> has replied in your conversation <strong>{1}</strong>.', h($username), h($conversationTitle));
                     } else {
                         $notification->text = __('<strong>{0}</strong> has replied in the conversation <strong>{1}</strong>.', h($username), h($conversationTitle));
                     }
                     $notification->link = Router::url(['controller' => 'conversations', 'action' => 'go', $notification->data['conversation']->last_message_id, 'prefix' => false]);
                     break;
                 case 'bot':
                     $notification->text = __('Welcome on <strong>{0}</strong>! You can now post your first comment in the blog.', \Cake\Core\Configure::read('Site.name'));
                     $notification->link = Router::url(['controller' => 'blog', 'action' => 'index', 'prefix' => false]);
                     $notification->icon = $notification->data['icon'];
                     break;
                 case 'badge':
                     $notification->text = __('You have unlock the badge "{0}".', $notification->data['badge']->name);
                     $notification->link = Router::url(['_name' => 'users-profile', 'id' => $notification->data['user']->id, 'slug' => $notification->data['user']->username, '#' => 'badges', 'prefix' => false]);
                     break;
             }
             return $notification;
         });
     });
 }

 /**
  * Check
  *
  * @param \Cake\Event\Event $event The beforeFind event that was fired.
  * @param \Cake\ORM\Query $query Query
  * @param \ArrayObject $options The options for the query
  * @return void
  */
 public function checkRecordAccess(Event $event, Query $query, ArrayObject $options)
 {
     $table = TableRegistry::get('RolesCapabilities.Capabilities');
     // current request parameters
     $request = $table->getCurrentRequest();
     // skip if current model does not match request's model
     if (array_diff(pluginSplit($event->subject()->registryAlias()), [$request['plugin'], $request['controller']])) {
         return;
     }
     // get capability owner type identifier
     $type = $table->getTypeOwner();
     // get user's action capabilities
     $userActionCapabilities = $table->getUserActionCapabilities();
     // skip if no user's action capabilities found or no user's action
     // owner specific capabilities found for current request's action
     if (empty($userActionCapabilities)) {
         return;
     }
     if (!isset($userActionCapabilities[$request['plugin']][$request['controller']][$request['action']][$type])) {
         return;
     }
     // set query where clause based on user's owner capabilities assignment fields
     foreach ($userActionCapabilities[$request['plugin']][$request['controller']][$request['action']][$type] as $userActionCapability) {
         $query->where([$userActionCapability->getField() => $table->getCurrentUser('id')]);
     }
 }

 /**
  * Before find any users, set contain to get user roles too.
  *
  * @param Event $event
  * @param Query $query
  * @param \ArrayObject $options
  * @param $primary
  */
 public function onBeforeFind(Event $event, $query, \ArrayObject $options, $primary)
 {
     $table = $event->subject();
     if ($table->alias() == 'Users') {
         $query->contain(['Roles']);
     }
 }

 /**
  * beforeFind callback
  *
  * inject where condition if context is 'tenant'
  *
  * @param \Cake\Event\Event $event The beforeFind event that was fired.
  * @param \Cake\ORM\Query $query The query.
  * @return void
  */
 public function beforeFind(Event $event, Query $query)
 {
     if (MTApp::getContext() == 'tenant') {
         $query->where([$this->_table->alias() . '.' . $this->config('foreign_key_field') . ' IN' => [$this->config('global_value'), MTApp::tenant()->id]]);
     }
     return $query;
 }

 public function beforeFind(Event $event, Query $query, \ArrayObject $options, $primary)
 {
     $order = $query->clause('order');
     if ($order === null || !count($order)) {
         $query->order(['Films.released DESC', 'Films.title']);
     }
 }

 /**
  * Returns query with applied filter
  *
  * @param \Cake\ORM\Query $query Query.
  * @param string $field Field name.
  * @param string $value Field value.
  * @param array $data Filters values.
  * @return \Cake\ORM\Query
  */
 protected function _buildQuery(Query $query, $field, $value, array $data = [])
 {
     if (is_array($value)) {
         $field .= ' IN';
     }
     return $query->where([$field => $value]);
 }

 /**
  * Find By Distance using spherical cosine law
  *
  * @param \Cake\ORM\Query $query Query to modify
  * @param array $options Options for the query
  * @throws GeoDistanceInvalidArgumentException If parameters are missing or invalid
  * @return \Cake\ORM\Query
  */
 public function findByDistance(Query $query, array $options)
 {
     // set up parameters
     $this->_validateOptions($options);
     $latitude = $options['latitude'];
     $longitude = $options['longitude'];
     $radius = $options['radius'];
     if (isset($options['units']) && !empty($options['units'])) {
         $units = $options['units'];
     } else {
         $units = $this->_config['units'];
     }
     $earthRadius = $this->_getEarthRadius($units);
     // construct query
     $sphericalCosineSql = "(:earth_radius * ACOS(\n            COS(RADIANS(:latitude)) *\n            COS(RADIANS({$this->_config['latitudeColumn']})) *\n            COS( RADIANS({$this->_config['longitudeColumn']}) - RADIANS(:longitude) ) +\n            SIN(RADIANS(:latitude)) *\n            SIN(RADIANS({$this->_config['latitudeColumn']}))\n        ) )";
     $connection = $query->connection();
     if ($connection->driver() instanceof Mysql) {
         $distance = "ROUND({$sphericalCosineSql}, 3)";
     } elseif ($connection->driver() instanceof Postgres) {
         $distance = "ROUND( CAST({$sphericalCosineSql} AS numeric), 3)";
     }
     $queryOptions = ['fields' => ['distance' => $distance], 'order' => ['distance ASC'], 'conditions' => ["{$distance} <= :radius"]];
     $query->find('all', $queryOptions)->autoFields(true)->bind(':earth_radius', $earthRadius, 'integer')->bind(':latitude', $latitude, 'float')->bind(':longitude', $longitude, 'float')->bind(':radius', $radius, 'float');
     return $query;
 }

 /**
  * Find Matches
  *
  * Assemble a query containing one or more MATCH() AGAINST() clauses
  * @param \Cake\ORM\Query $query Query to modify
  * @param array $options Options for the query
  * @throws SearchableException If keys 'match' or 'against' are not set
  * @throws SearchableException If columns are invalid
  * @return \Cake\ORM\Query
  */
 public function findMatches(Query $query, array $options)
 {
     $conditions = [];
     $options = array_values($options);
     //assemble MATCH() AGAINST() clauses
     foreach ($options as $key => $option) {
         if (!isset($option['match']) || !isset($option['against'])) {
             throw new SearchableException("Keys 'match' and 'against' must be set");
         }
         if ($this->_validateColumns($option['match'])) {
             $conditions[$key] = "MATCH({$option['match']}) AGAINST (:match{$key} ";
             if (isset($option['mode'])) {
                 if (in_array($option['mode'], $this->_modesWhitelist)) {
                     $conditions[$key] .= $option['mode'] . ' ';
                 }
             }
             $conditions[$key] .= ")";
         }
     }
     $query->find('all', ['conditions' => $conditions]);
     //bind params
     foreach ($options as $key => $option) {
         $query->bind(":match{$key}", $option['against']);
     }
     return $query;
 }

 public function findMetCustomers(Query $query, array $options)
 {
     $query->contain(['Users.Customers'])->matching('Users', function ($q) use($options) {
         return $q->where(['Users.id' => $options['Users.id']]);
     });
     return $query;
 }

 /**
  * Find all tasks grouped by project
  * 
  * @param Query $query
  * @param type $options
  * @return Query
  */
 public function findTasksByProject(Query $query, $options = [])
 {
     $options += ['where' => NULL];
     $query->find('list', ['groupField' => 'project_id']);
     $query->where($options['where']);
     return $query;
 }

 public function beforeFind(Event $event, Query $query, ArrayObject $options, $primary)
 {
     if (!array_key_exists('getRelated', $options) || !$options['getRelated']) {
         //Jen pokud se mají related stahovat
         return true;
     }
     $attachedTables = $this->_InRelatedIndexBehavior->getTablesWithBehaviorNames();
     /** @var \Cake\ORM\Table $attachedTable */
     foreach ($attachedTables as $tableName) {
         $modelName = Inflector::camelize($tableName);
         $query->contain(['Related' . $modelName => []]);
     }
     $query->formatResults(function ($results) {
         return $results->map(function ($row) {
             $temp = $row->toArray();
             $related = [];
             foreach ($temp as $key => $item) {
                 if (preg_match('/related-.*/', $key)) {
                     foreach ($row->{$key} as $id => $similar) {
                         $table_name = explode('-', $key);
                         $row->{$key}[$id]->table_name = end($table_name);
                     }
                     $related = array_merge($related, $row->{$key});
                     unset($row->{$key});
                 }
             }
             $row->related = $related;
             return $row;
         });
     });
     return true;
 }

 /**
  * Find panels by requestid
  *
  * @param Cake\ORM\Query $query The query
  * @param array $options The options to use.
  * @return Cake\ORM\Query The query.
  * @throws \RuntimeException
  */
 public function findByRequest(Query $query, array $options)
 {
     if (empty($options['requestId'])) {
         throw new \RuntimeException('Missing request id in findByRequest.');
     }
     return $query->where(['Panels.request_id' => $options['requestId']])->order(['Panels.title' => 'ASC']);
 }