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 | |
||
$fields | array | Cake\Database\ExpressionInterface | string | |
fields to be added to the list. |
$overwrite | boolean | whether to reset fields with passed list or not |
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']); }
/** * 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; }
/** * 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; }
/** * 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; }
/** * 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); }
public function findAverageLifeExpectancy(Query $query) { return $query->select(['average_exp' => $query->func()->avg('life_expectancy')]); }
/** * 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; }
public function findOfficialLanguages(Query $query) { return $query->select(['language'])->where(['is_official' => true])->distinct(['language'])->hydrate(false); }
/** * 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']); }
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; }
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; }
/** * 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]); }
/** * 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']); }
/** * 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())); } }
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); }
/** * 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']); }
/** * 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()); }
/** * 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); }
/** * 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']); }]); }
/** * 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']); }
public function findAuth(Query $query, array $option) { $query->select(['id', 'email', 'password'])->where(['Users.active' => 1]); return $query; }
/** * 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']); }); }