select() public method

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.
public select ( array | Cake\Database\ExpressionInterface | string | Table | Cake\ORM\Association $fields = [], boolean $overwrite = false )
$fields array | Cake\Database\ExpressionInterface | string | Table | Cake\ORM\Association fields to be added to the list.
$overwrite boolean whether to reset fields with passed list or not
示例#1
1
 public function findWithCount(Query $query, array $options)
 {
     $query->select(['count' => $query->func()->count('Users.id')]);
     $query->matching('Users');
     $query->group(['UserRoles.id']);
     return $query;
 }
 /**
  * Custom finder for distance.
  *
  * Options:
  * - lat (required)
  * - lng (required)
  * - tableName
  * - distance
  *
  * @param \Cake\ORM\Query $query Query.
  * @param array $options Array of options as described above
  * @return \Cake\ORM\Query
  */
 public function findDistance(Query $query, array $options)
 {
     $options += ['tableName' => null];
     $sql = $this->distance($options['lat'], $options['lng'], null, null, $options['tableName']);
     $query->select(['distance' => $query->newExpr($sql)]);
     if (isset($options['distance'])) {
         // Some SQL versions cannot reuse the select() distance field, so we better reuse the $sql snippet
         $query->where(['(' . $sql . ') <' => $options['distance']]);
     }
     return $query->order(['distance' => 'ASC']);
 }
示例#3
0
 /**
  * Custom finder
  *
  * @param \Cake\ORM\Query $query The query to find with
  * @param array $options The options to find with
  * @return \Cake\ORM\Query The query builder
  */
 public function findAuth(Query $query, array $options)
 {
     $query->select(['id', 'username', 'password']);
     if (!empty($options['return_created'])) {
         $query->select(['created']);
     }
     return $query;
 }
示例#4
0
 /**
  * Custom finder
  *
  * @param \Cake\ORM\Query $query The query to find with
  * @param array $options The options to find with
  * @return \Cake\ORM\Query The query builder
  */
 public function findAuth(Query $query, array $options)
 {
     $query->select(['id', 'username', 'password']);
     return $query;
 }
示例#5
0
 /**
  * Custom finder for select all offers.
  *
  * @param \Cake\ORM\Query $query The query finder.
  *
  * @return \Cake\ORM\Query
  */
 public function findOffers(Query $query)
 {
     return $query->select(['period', 'price', 'tax', 'currency_symbol'])->order(['price' => 'ASC']);
 }
 /**
  * Add translation fields to query
  *
  * If the query is using autofields (directly or implicitly) add the
  * main table's fields to the query first.
  *
  * Only add translations for fields that are in the main table, always
  * add the locale field though.
  *
  * @param \Cake\ORM\Query $query the query to check
  * @param array $config the config to use for adding fields
  * @return bool Whether a join to the translation table is required
  */
 protected function _addFieldsToQuery(Query $query, array $config)
 {
     $select = $query->clause('select');
     if (!$select) {
         return true;
     }
     $alias = $config['mainTableAlias'];
     $joinRequired = false;
     foreach ($this->_translationFields() as $field) {
         if (array_intersect($select, [$field, "{$alias}.{$field}"])) {
             $joinRequired = true;
             $query->select($query->aliasField($field, $config['hasOneAlias']));
         }
     }
     if ($joinRequired) {
         $query->select($query->aliasField('locale', $config['hasOneAlias']));
     }
     return $joinRequired;
 }
 /**
  * Tests that default fields for associations are added to the select clause when
  * none is specified
  *
  * @return void
  */
 public function testContainToFieldsDefault()
 {
     $contains = ['clients' => ['orders']];
     $query = new Query($this->connection, $this->table);
     $query->select()->contain($contains)->sql();
     $select = $query->clause('select');
     $expected = ['foo__id' => 'foo.id', 'clients__name' => 'clients.name', 'clients__id' => 'clients.id', 'clients__phone' => 'clients.phone', 'orders__id' => 'orders.id', 'orders__total' => 'orders.total', 'orders__placed' => 'orders.placed'];
     $expected = $this->_quoteArray($expected);
     $this->assertEquals($expected, $select);
     $contains['clients']['fields'] = ['name'];
     $query = new Query($this->connection, $this->table);
     $query->select('foo.id')->contain($contains)->sql();
     $select = $query->clause('select');
     $expected = ['foo__id' => 'foo.id', 'clients__name' => 'clients.name'];
     $expected = $this->_quoteArray($expected);
     $this->assertEquals($expected, $select);
     $contains['clients']['fields'] = [];
     $contains['clients']['orders']['fields'] = false;
     $query = new Query($this->connection, $this->table);
     $query->select()->contain($contains)->sql();
     $select = $query->clause('select');
     $expected = ['foo__id' => 'foo.id', 'clients__id' => 'clients.id', 'clients__name' => 'clients.name', 'clients__phone' => 'clients.phone'];
     $expected = $this->_quoteArray($expected);
     $this->assertEquals($expected, $select);
 }
 /**
  * Custom finder for distance.
  *
  * Options:
  * - lat (required)
  * - lng (required)
  * - tableName
  * - distance
  * - sort
  *
  * @param \Cake\ORM\Query $query Query.
  * @param array $options Array of options as described above
  * @return \Cake\ORM\Query
  */
 public function findDistance(Query $query, array $options)
 {
     $options += ['tableName' => null, 'sort' => true];
     $sql = $this->distanceExpr($options['lat'], $options['lng'], null, null, $options['tableName']);
     if ($query->autoFields() === null) {
         $query->autoFields(true);
     }
     $query->select(['distance' => $query->newExpr($sql)]);
     if (isset($options['distance'])) {
         // Some SQL versions cannot reuse the select() distance field, so we better reuse the $sql snippet
         $query->where(function ($exp) use($sql, $options) {
             return $exp->lt($sql, $options['distance']);
         });
     }
     if ($options['sort']) {
         $sort = $options['sort'] === true ? 'ASC' : $options['sort'];
         $query->order(['distance' => $sort]);
     }
     return $query;
 }
示例#9
0
 /**
  * Helper function used to conditionally append fields to the select clause of
  * a query from the fields found in another query object.
  *
  * @param \Cake\ORM\Query $query the query that will get the fields appended to
  * @param \Cake\ORM\Query $surrogate the query having the fields to be copied from
  * @param array $options options passed to the method `attachTo`
  * @return void
  */
 protected function _appendFields($query, $surrogate, $options)
 {
     $options['fields'] = $surrogate->clause('select') ?: $options['fields'];
     $target = $this->_targetTable;
     if (empty($options['fields'])) {
         $f = isset($options['fields']) ? $options['fields'] : null;
         if ($options['includeFields'] && ($f === null || $f !== false)) {
             $options['fields'] = $target->schema()->columns();
         }
     }
     if (!empty($options['fields'])) {
         $query->select($query->aliasFields($options['fields'], $target->alias()));
     }
 }
 /**
  * Method that adds SELECT clause to the Query to only include
  * action fields (as defined in the csv file).
  *
  * @param  \Cake\ORM\Query   $query Query object
  * @param  \Cake\Event\Event $event The event
  * @return void
  */
 protected function _selectActionFields(Query $query, Event $event)
 {
     if (!in_array($event->subject()->request->query('format'), [static::FORMAT_DATATABLES])) {
         return;
     }
     $fields = $this->_getActionFields($event->subject()->request);
     if (empty($fields)) {
         return;
     }
     $primaryKey = $event->subject()->{$event->subject()->name}->primaryKey();
     // always include primary key, useful for menus URLs
     if (!in_array($primaryKey, $fields)) {
         array_push($fields, $primaryKey);
     }
     $query->select($this->_databaseFields($fields, $event), true);
 }
示例#11
0
 public function findAverageLifeExpectancy(Query $query)
 {
     return $query->select(['average_exp' => $query->func()->avg('life_expectancy')]);
 }
示例#12
0
 /**
  * Find usado primeiramente pela autenticação
  */
 public function findAdmin(Query $query, array $options)
 {
     $query->select(['id', 'email', 'password'])->where(['active' => 1, 'role' => 1]);
     return $query;
 }
示例#13
0
 public function findOfficialLanguages(Query $query)
 {
     return $query->select(['language'])->where(['is_official' => true])->distinct(['language'])->hydrate(false);
 }
示例#14
0
 /**
  * Custom finder for distance.
  *
  * Used to be a virtual field in 2.x via setDistanceAsVirtualField()
  *
  * Options:
  * - lat (required)
  * - lng (required)
  * - tableName
  * - distance
  *
  * @param \Cake\ORM\Query $query Query.
  * @param array $options Array of options as described above
  * @return \Cake\ORM\Query
  */
 public function findDistance(Query $query, array $options)
 {
     $options += array('tableName' => null);
     $sql = $this->distance($options['lat'], $options['lng'], null, null, $options['tableName']);
     $query->select(['distance' => $query->newExpr($sql)]);
     if (isset($options['distance'])) {
         $query->where(['distance <' => $options['distance']]);
     }
     return $query->order(['distance' => 'ASC']);
 }
示例#15
0
 public function findAuth(\Cake\ORM\Query $query, array $options)
 {
     //NOT CURRENTLY USED!!!
     $query->select(['id', 'username', 'password', 'is_active'])->where(['Users.is_active' => 1]);
     return $query;
 }
示例#16
0
 public function findAuth(\Cake\ORM\Query $query, array $options)
 {
     $query->select(['id', 'first_name', 'last_name', 'email', 'password', 'role'])->where(['Users.active' => 1]);
     return $query;
 }
示例#17
0
 /**
  * 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];
 }
 public function findAveragePerYear(Query $query, $options = [])
 {
     $year = $query->func()->year(['Salaries.from_date' => 'literal']);
     return $query->select(['year' => $year])->find('average')->group([$year]);
 }
示例#19
0
 /**
  * Finds surveys can have their responses automatically imported,
  * sorted with the surveys whose responses have been least-recently
  * imported first.
  *
  * @param Query $query Query
  * @param array $options Query options
  * @return Query
  */
 public function findAutoImportCandidate(Query $query, array $options)
 {
     return $query->select(['id'])->where([function ($exp, $q) {
         return $exp->isNotNull('Surveys.sm_id');
     }, 'active' => 1, 'OR' => [['Surveys.type' => 'official', function ($exp, $q) {
         return $exp->gt('Communities.score', '1');
     }, function ($exp, $q) {
         return $exp->lt('Communities.score', '3');
     }], ['Surveys.type' => 'organization', function ($exp, $q) {
         return $exp->gt('Communities.score', '2');
     }, function ($exp, $q) {
         return $exp->lt('Communities.score', '4');
     }]]])->join(['table' => 'communities', 'alias' => 'Communities', 'type' => 'LEFT', 'conditions' => ['Communities.id = Surveys.community_id']])->order(['responses_checked' => 'ASC']);
 }
示例#20
0
 /**
  * Helper function used to conditionally append fields to the select clause of
  * a query from the fields found in another query object.
  *
  * @param \Cake\ORM\Query $query the query that will get the fields appended to
  * @param \Cake\ORM\Query $surrogate the query having the fields to be copied from
  * @param array $options options passed to the method `attachTo`
  * @return void
  */
 protected function _appendFields($query, $surrogate, $options)
 {
     $fields = $surrogate->clause('select') ?: $options['fields'];
     $target = $this->_targetTable;
     $autoFields = $surrogate->autoFields();
     if ($query->eagerLoader()->autoFields() === false) {
         return;
     }
     if (empty($fields) && !$autoFields) {
         if ($options['includeFields'] && ($fields === null || $fields !== false)) {
             $fields = $target->schema()->columns();
         }
     }
     if ($autoFields === true) {
         $fields = array_merge((array) $fields, $target->schema()->columns());
     }
     if (!empty($fields)) {
         $query->select($query->aliasFields($fields, $target->alias()));
     }
 }
示例#21
0
 public function findCountriesWithProbability(Query $query, array $options)
 {
     $query->select(['Countries.name', 'Languages.percentage'])->matching('Languages', function ($q) use($options) {
         return $q->where(['Languages.percentage >=' => $options['percentage'], 'Languages.language' => $options['language']]);
     });
     return $query;
 }
 /**
  * Tests loding hasOne with composite keys
  *
  * @dataProvider strategiesProviderHasOne
  * @return void
  */
 public function testHasOneEager($strategy)
 {
     $table = TableRegistry::get('SiteAuthors');
     $table->hasOne('SiteArticles', ['propertyName' => 'first_article', 'strategy' => $strategy, 'foreignKey' => ['author_id', 'site_id']]);
     $query = new Query($this->connection, $table);
     $results = $query->select()->where(['SiteAuthors.id IN' => [1, 3]])->contain('SiteArticles')->hydrate(false)->toArray();
     $expected = [['id' => 1, 'name' => 'mark', 'site_id' => 1, 'first_article' => ['id' => 1, 'author_id' => 1, 'site_id' => 1, 'title' => 'First Article', 'body' => 'First Article Body']], ['id' => 3, 'name' => 'jose', 'site_id' => 2, 'first_article' => ['id' => 2, 'author_id' => 3, 'site_id' => 2, 'title' => 'Second Article', 'body' => 'Second Article Body']]];
     $this->assertEquals($expected, $results);
 }
示例#23
0
 /**
  * Custom finder for select full fields.
  *
  * @param \Cake\ORM\Query $query The query finder.
  *
  * @return \Cake\ORM\Query
  */
 public function findFull(Query $query)
 {
     return $query->select(['first_name', 'last_name', 'username', 'avatar', 'slug', 'role', 'facebook', 'twitter', 'signature', 'created', 'last_login']);
 }
示例#24
0
 /**
  * Test fetching results from a qurey with a two custom formatters
  *
  * @return void
  */
 public function testQueryWithStackedFormatters()
 {
     $table = TableRegistry::get('authors');
     $query = new Query($this->connection, $table);
     $query->select()->formatResults(function ($results) {
         $this->assertInstanceOf('Cake\\ORM\\ResultSet', $results);
         return $results->indexBy('id');
     });
     $query->formatResults(function ($results) {
         return $results->extract('name');
     });
     $expected = [1 => 'mariano', 2 => 'nate', 3 => 'larry', 4 => 'garrett'];
     $this->assertEquals($expected, $query->toArray());
 }
示例#25
0
文件: UsersTable.php 项目: Xety/Xeta
 /**
  * Custom finder for select full fields.
  *
  * @param \Cake\ORM\Query $query The query finder.
  *
  * @return \Cake\ORM\Query
  */
 public function findFull(Query $query)
 {
     return $query->select(['id', 'first_name', 'last_name', 'username', 'avatar', 'group_id', 'blog_articles_comment_count', 'blog_article_count', 'facebook', 'twitter', 'signature', 'created', 'last_login']);
 }
 /**
  * Appends any conditions required to load the relevant set of records in the
  * target table query given a filter key and some filtering values when the
  * filtering needs to be done using a subquery.
  *
  * @param \Cake\ORM\Query $query Target table's query
  * @param string $key the fields that should be used for filtering
  * @param \Cake\ORM\Query $subquery The Subquery to use for filtering
  * @return \Cake\ORM\Query
  */
 public function _addFilteringJoin($query, $key, $subquery)
 {
     $filter = [];
     $aliasedTable = $this->source()->alias();
     foreach ($subquery->clause('select') as $aliasedField => $field) {
         $filter[] = new IdentifierExpression($field);
     }
     $subquery->select($filter, true);
     if (is_array($key)) {
         $conditions = $this->_createTupleCondition($query, $key, $filter, '=');
     } else {
         $filter = current($filter);
     }
     $conditions = isset($conditions) ? $conditions : $query->newExpr([$key => $filter]);
     return $query->innerJoin([$aliasedTable => $subquery], $conditions);
 }
示例#27
0
 /**
  * Find users including their group name.
  *
  * @param Query $query The query to decorate.
  * @return $this|array
  */
 public function findWithGroupName(Query $query)
 {
     return $query->select(['id', 'username', 'email', 'verified', 'active'])->contain(['Groups' => function (Query $q) {
         return $q->select(['name']);
     }]);
 }
示例#28
0
 /**
  * Custom finder for select full fields.
  *
  * @param \Cake\ORM\Query $query The query finder.
  *
  * @return \Cake\ORM\Query
  */
 public function findFull(Query $query)
 {
     return $query->select(['id', 'first_name', 'last_name', 'username', 'avatar', 'slug', 'group_id', 'forum_post_count', 'forum_thread_count', 'forum_like_received', 'blog_articles_comment_count', 'blog_article_count', 'facebook', 'twitter', 'signature', 'end_subscription', 'created', 'last_login']);
 }
示例#29
-1
 public function findAuth(Query $query, array $option)
 {
     $query->select(['id', 'email', 'password'])->where(['Users.active' => 1]);
     return $query;
 }
示例#30
-14
 /**
  * Sets up a query object so results appear as an indexed array, useful for any
  * place where you would want a list such as for populating input select boxes.
  *
  * When calling this finder, the fields passed are used to determine what should
  * be used as the array key, value and optionally what to group the results by.
  * By default the primary key for the model is used for the key, and the display
  * field as value.
  *
  * The results of this finder will be in the following form:
  *
  * ```
  * [
  *  1 => 'value for id 1',
  *  2 => 'value for id 2',
  *  4 => 'value for id 4'
  * ]
  * ```
  *
  * You can specify which property will be used as the key and which as value
  * by using the `$options` array, when not specified, it will use the results
  * of calling `primaryKey` and `displayField` respectively in this table:
  *
  * ```
  * $table->find('list', [
  *  'keyField' => 'name',
  *  'valueField' => 'age'
  * ]);
  * ```
  *
  * Results can be put together in bigger groups when they share a property, you
  * can customize the property to use for grouping by setting `groupField`:
  *
  * ```
  * $table->find('list', [
  *  'groupField' => 'category_id',
  * ]);
  * ```
  *
  * When using a `groupField` results will be returned in this format:
  *
  * ```
  * [
  *  'group_1' => [
  *      1 => 'value for id 1',
  *      2 => 'value for id 2',
  *  ]
  *  'group_2' => [
  *      4 => 'value for id 4'
  *  ]
  * ]
  * ```
  *
  * @param \Cake\ORM\Query $query The query to find with
  * @param array $options The options for the find
  * @return \Cake\ORM\Query The query builder
  */
 public function findList(Query $query, array $options)
 {
     $options += ['keyField' => $this->primaryKey(), 'valueField' => $this->displayField(), 'groupField' => null];
     if (isset($options['idField'])) {
         $options['keyField'] = $options['idField'];
         unset($options['idField']);
         trigger_error('Option "idField" is deprecated, use "keyField" instead.', E_USER_WARNING);
     }
     if (!$query->clause('select') && !is_object($options['keyField']) && !is_object($options['valueField']) && !is_object($options['groupField'])) {
         $fields = array_merge((array) $options['keyField'], (array) $options['valueField'], (array) $options['groupField']);
         $columns = $this->schema()->columns();
         if (count($fields) === count(array_intersect($fields, $columns))) {
             $query->select($fields);
         }
     }
     $options = $this->_setFieldMatchers($options, ['keyField', 'valueField', 'groupField']);
     return $query->formatResults(function ($results) use($options) {
         return $results->combine($options['keyField'], $options['valueField'], $options['groupField']);
     });
 }