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']);
});
}
