### Example:
Bring articles' author information
$query->contain('Author');
Also bring the category and tags associated to each article
$query->contain(['Category', 'Tag']);
Associations can be arbitrarily nested using dot notation or nested arrays,
this allows this object to calculate joins or any additional queries that
must be executed to bring the required associated data.
### Example:
Eager load the product info, and for each product load other 2 associations
$query->contain(['Product' => ['Manufacturer', 'Distributor']);
Which is equivalent to calling
$query->contain(['Products.Manufactures', 'Products.Distributors']);
For an author query, load his region, state and country
$query->contain('Regions.States.Countries');
It is possible to control the conditions and fields selected for each of the
contained associations:
### Example:
$query->contain(['Tags' => function ($q) {
return $q->where(['Tags.is_popular' => true]);
}]);
$query->contain(['Products.Manufactures' => function ($q) {
return $q->select(['name'])->where(['Manufactures.active' => true]);
}]);
Each association might define special options when eager loaded, the allowed
options that can be set per association are:
- foreignKey: Used to set a different field to match both tables, if set to false
no join conditions will be generated automatically. false can only be used on
joinable associations and cannot be used with hasMany or belongsToMany associations.
- fields: An array with the fields that should be fetched from the association.
- finder: The finder to use when loading associated records. Either the name of the
finder as a string, or an array to define options to pass to the finder.
- queryBuilder: Equivalent to passing a callable instead of an options array.
### Example:
Set options for the hasMany articles that will be eagerly loaded for an author
$query->contain([
'Articles' => [
'fields' => ['title', 'author_id']
]
]);
Finders can be configured to use options.
Retrieve translations for the articles, but only those for the en and es locales
$query->contain([
'Articles' => [
'finder' => [
'translations' => [
'locales' => ['en', 'es']
]
]
]
]);
When containing associations, it is important to include foreign key columns.
Failing to do so will trigger exceptions.
Use special join conditions for getting an Articles's belongsTo 'authors'
$query->contain([
'Authors' => [
'foreignKey' => false,
'queryBuilder' => function ($q) {
return $q->where(...); // Add full filtering conditions
}
]
]);
If called with no arguments, this function will return an array with
with the list of previously configured associations to be contained in the
result.
If called with an empty first argument and $override is set to true, the
previous list will be emptied.
| public contain ( array | string | null $associations = null, boolean $override = false ) : array | $this | ||
| $associations | array | string | null | List of table aliases to be queried. |
| $override | boolean | Whether override previous list with the one passed defaults to merging previous list with the new one. |
| Résultat | array | $this | |
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;
}
public function findWithContain(Query $query, array $options = [])
{
if (!empty($this->_contain)) {
$query->contain($this->_contain);
}
return $query;
}
/**
* {@inheritDoc}
*/
public function beforePaginate(Event $event, Query $query)
{
$query->contain($this->_getAssociations($event));
$this->_filterByConditions($query, $event);
$this->_selectActionFields($query, $event);
$this->_handleDtSorting($query, $event);
}
/**
* 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']);
}
}
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;
}
public function findParticipationsForStudent(Query $query, array $options)
{
return $query->contain(['Tests' => function ($q) {
return $q->select(['Tests.datepreuve']);
}, 'Tests.Subjects' => function ($q) {
return $q->select(['Subjects.libelle', 'Subjects.coeff']);
}])->where($options)->hydrate(false);
}
public function findTagged(Query $query, array $options)
{
$query->contain(['Users', 'Users.AccountParameters', 'Hashtags']);
$query->matching('Hashtags', function ($q) use($options) {
return $q->where(['Hashtags.name' => $options['tag_name']]);
});
$query->order(['Tweets.created' => 'DESC']);
return $query;
}
/**
* Finds all memberships that will expire in the next 24 hours, are marked
* for automatic renewal, and have not been renewed or canceled.
*
* @param Query $query
* @param array $options
* @return Query
*/
public function findToAutoRenew(Query $query, array $options)
{
return $query->contain(['Users' => function ($q) {
return $q->select(['id', 'name', 'email', 'stripe_customer_id']);
}, 'MembershipLevels' => function ($q) {
return $q->select(['id', 'name', 'cost']);
}])->where(function ($exp, $q) {
return $exp->isNull('canceled');
})->where(function ($exp, $q) {
return $exp->lte('expires', date('Y-m-d H:i:s', strtotime('+1 day')));
})->where(['auto_renew' => 1])->order(['expires' => 'ASC']);
}
/**
* Get CakePHP property
*
* @param string $dataName Column data name
*
* @throws Exception
* @return array|string
*/
protected function getProperty($dataName)
{
$dataName = explode('.', trim($dataName));
if (count($dataName) != 2) {
throw new Exception('You are set invalid date.');
}
$tableAlias = $dataName[0];
$colName = $dataName[1];
if ($this->query->repository()->alias() == $tableAlias) {
return $colName;
} elseif (array_key_exists($tableAlias, $this->query->contain())) {
return ['propertyPath' => $this->query->eagerLoader()->normalized($this->query->repository())[$tableAlias]['propertyPath'], 'field' => $colName];
}
}
/**
* Dynamic finder that loads all users for a thread without me
*
* @param \Cake\ORM\Query $query the original query to append to
* @param array $users the list of users to be ignored
* @return \Cake\ORM\Query The amended query
*/
public function findReadStatus(Query $query, array $users)
{
$query->contain(['Messages' => function ($q) use($users) {
return $q->find('readStatus', $users);
}]);
return $query->map(function ($thread) {
$opened = false;
foreach ($thread['messages'] as $message) {
if ($message['message_read_statuses'][0]['opened']) {
$opened = true;
break;
}
}
$thread['opened'] = $opened;
return $thread;
});
}
/**
* Attaches comments to each entity on find operation.
*
* @param \Cake\Event\Event $event The event that was triggered
* @param \Cake\ORM\Query $query The query object
* @param array $options Additional options as an array
* @param bool $primary Whether is find is a primary query or not
* @return void
*/
public function beforeFind(Event $event, $query, $options, $primary)
{
if ($this->_enabled && $query->count() > 0) {
$pk = $this->_table->primaryKey();
$tableAlias = Inflector::underscore($this->_table->alias());
$query->contain(['Comments' => function ($query) {
return $query->find('threaded')->contain(['Users'])->order($this->config('order'));
}]);
if ($this->config('count') || isset($options['comments_count']) && $options['comments_count'] === true) {
$query->formatResults(function ($results) use($pk, $tableAlias) {
return $results->map(function ($entity) use($pk, $tableAlias) {
$entityId = $entity->{$pk};
$count = TableRegistry::get('Comment.Comments')->find()->where(['entity_id' => $entityId, 'table_alias' => $tableAlias])->count();
$entity->set('comments_count', $count);
return $entity;
});
});
}
}
}
/**
* [beforeFind description]
*
* @param Event $event [description]
* @param Query $query [description]
* @param array $options
*
* @return $this|array|Query [type] [description]
*
* ### Options
* `images` When setting images to false nothing will be added to the query and no image fields will be returned in the resultset and will probably
* speed up overall performance
*/
public function beforeFind(Event $event, Query $query, $options = [])
{
if (isset($options['images']) && !$options['images']) {
return $query;
}
$fields = $this->config('fields');
$contain = $conditions = [];
foreach ($fields as $field => $type) {
$field = $this->_fieldName($field);
$contain[$field] = $conditions;
}
/**
* @param $row
* @param $key
* @param $mapReduce MapReduce
*/
$mapper = function ($row, $key, $mapReduce) use($fields) {
foreach ($fields as $field => $type) {
$name = $this->_fieldName($field, false);
$image = isset($row[$name]) ? $row[$name] : null;
// make sure we set the correct registry alias for the entity so
// we can access the entity's repository from the ImageHelper
if (!empty($image)) {
if (is_array($image)) {
foreach ($image as &$imageEntity) {
$this->_setEntitySource($imageEntity);
}
} else {
$this->_setEntitySource($image);
}
}
if ($image === null) {
unset($row[$name]);
continue;
}
$row[$field] = $image;
unset($row[$name]);
}
if ($row instanceof Entity) {
$row->clean();
}
$mapReduce->emitIntermediate($row, $key);
};
/**
* @param $items
* @param $key
* @param $mapReduce MapReduce
*/
$reducer = function ($items, $key, $mapReduce) {
if (isset($items[0])) {
$mapReduce->emit($items[0], $key);
}
};
$request = Router::getRequest();
if (!isset($request->params['prefix']) || $request->params['prefix'] !== 'admin') {
//TODO zuniverzálnit
foreach ($contain as $key => &$item) {
$item['conditions'] = [$key . '.active' => true];
}
}
$q = $query->contain($contain)->mapReduce($mapper, $reducer);
return $q;
}
public function findOfHired(Query $query, $options = [])
{
return $query->contain(['Employees'])->where(['Salaries.to_date IS' => null]);
}
public function findWithOfficialLanguage(Query $query)
{
return $query->contain('OfficialLanguages');
}
/**
* Custom finder method
* users translating to/from given language
*
* @param Query $query
* @param array $options ['language_id'] == language_id, ['target'] == true (target language) false (source language)
* @return Query
*/
public function findLanguage(Query $query, array $options)
{
$query->contain(['LanguagesDirection'])->matching('LanguagesDirection', function (\Cake\ORM\Query $query) use($options) {
return $query->where(['LanguagesDirection.language_id' => $options['language_id'], 'LanguagesDirection.target' => $options['target']]);
});
return $query;
}
public function beforeFind(Event $event, Query $query, ArrayObject $options, $primary)
{
$query->contain(['Attachments']);
}
/**
* Custom finder method used to retrieve all versions for the found records.
*
* Versioned values will be found for each entity under the property `_versions`.
*
* ### Example:
*
* {{{
* $article = $articles->find('versions')->first();
* $firstVersion = $article->get('_versions')[1];
* }}}
*
* @param \Cake\ORM\Query $query The original query to modify
* @param array $options Options
* @return \Cake\ORM\Query
*/
public function findVersions(Query $query, array $options)
{
$association = $this->versionAssociation();
$name = $association->name();
return $query->contain([$name => function ($q) use($name, $options, $query) {
if (!empty($options['primaryKey'])) {
$foreignKey = (array) $this->_config['foreignKey'];
$aliasedFK = [];
foreach ($foreignKey as $field) {
$aliasedFK[] = "{$name}.{$field}";
}
$conditions = array_combine($aliasedFK, (array) $options['primaryKey']);
$q->where($conditions);
}
if (!empty($options['versionId'])) {
$q->where(["{$name}.version_id IN" => $options['versionId']]);
}
$q->where(["{$name}.field IN" => $this->_fields()]);
return $q;
}])->formatResults([$this, 'groupVersions'], $query::PREPEND);
}
/**
* Applies all attachable associations to `$query` out of the containments found
* in the `$surrogate` query.
*
* Copies all contained associations from the `$surrogate` query into the
* passed `$query`. Containments are altered so that they respect the associations
* chain from which they originated.
*
* @param \Cake\ORM\Query $query the query that will get the associations attached to
* @param \Cake\ORM\Query $surrogate the query having the containments to be attached
* @param array $options options passed to the method `attachTo`
* @return void
*/
protected function _bindNewAssociations($query, $surrogate, $options)
{
$contain = $surrogate->contain();
$target = $this->_targetTable;
if (!$contain) {
return;
}
$loader = $surrogate->eagerLoader();
$loader->attachAssociations($query, $target, $options['includeFields']);
$newBinds = [];
foreach ($contain as $alias => $value) {
$newBinds[$options['aliasPath'] . '.' . $alias] = $value;
}
$query->contain($newBinds);
}
/**
* Tests that applying array options to a query will convert them
* to equivalent function calls with the correspondent array values
*
* @return void
*/
public function testApplyOptions()
{
$this->table->belongsTo('articles');
$typeMap = new TypeMap(['foo.id' => 'integer', 'id' => 'integer', 'foo__id' => 'integer', 'articles.id' => 'integer', 'articles__id' => 'integer', 'articles.author_id' => 'integer', 'articles__author_id' => 'integer', 'author_id' => 'integer', 'articles.title' => 'string', 'articles__title' => 'string', 'title' => 'string', 'articles.body' => 'text', 'articles__body' => 'text', 'body' => 'text', 'articles.published' => 'string', 'articles__published' => 'string', 'published' => 'string']);
$options = ['fields' => ['field_a', 'field_b'], 'conditions' => ['field_a' => 1, 'field_b' => 'something'], 'limit' => 1, 'order' => ['a' => 'ASC'], 'offset' => 5, 'group' => ['field_a'], 'having' => ['field_a >' => 100], 'contain' => ['articles'], 'join' => ['table_a' => ['conditions' => ['a > b']]]];
$query = new Query($this->connection, $this->table);
$query->applyOptions($options);
$this->assertEquals(['field_a', 'field_b'], $query->clause('select'));
$expected = new QueryExpression($options['conditions'], $typeMap);
$result = $query->clause('where');
$this->assertEquals($expected, $result);
$this->assertEquals(1, $query->clause('limit'));
$expected = new QueryExpression(['a > b'], $typeMap);
$result = $query->clause('join');
$this->assertEquals(['table_a' => ['alias' => 'table_a', 'type' => 'INNER', 'conditions' => $expected]], $result);
$expected = new OrderByExpression(['a' => 'ASC']);
$this->assertEquals($expected, $query->clause('order'));
$this->assertEquals(5, $query->clause('offset'));
$this->assertEquals(['field_a'], $query->clause('group'));
$expected = new QueryExpression($options['having'], $typeMap);
$this->assertEquals($expected, $query->clause('having'));
$expected = ['articles' => []];
$this->assertEquals($expected, $query->contain());
}
/**
* Retrieve time records based upon user and timeframe
*
* Requires the pass of the request as the options element
* pass_params[0] is the number of days to return
* pass_params[1] is the user, or ALL for all users
*
* If there is an Auth user, and no user param is passed,
* the Auth user is used
*
* @param Query $query
* @param array $options
* @return Query
*/
public function findUserTimes(Query $query, array $options)
{
$session_user = $options['request']->session()->read('Auth.User.id');
$default = [0 => 7, 1 => is_null($session_user) ? 'ALL' : $session_user];
list($days, $user) = $options['request']->params['pass'] + $default;
$query->order(['Times.time_in' => 'DESC']);
$query->contain(['Users', 'Projects']);
$query->where(['Times.time_in >=' => new DateTime("-{$days} days")]);
if ($user != 'ALL') {
$query->where(['Times.user_id' => $user]);
}
return $query;
}
public function findFemale(Query $query, $options = [])
{
return $query->contain(['Employees'])->where(['Employees.gender' => 'F']);
}
/**
* Custom finder method used to retrieve all translations for the found records.
* Fetched translations can be filtered by locale by passing the `locales` key
* in the options array.
*
* Translated values will be found for each entity under the property `_translations`,
* containing an array indexed by locale name.
*
* ### Example:
*
* ```
* $article = $articles->find('translations', ['locales' => ['eng', 'deu'])->first();
* $englishTranslatedFields = $article->get('_translations')['eng'];
* ```
*
* If the `locales` array is not passed, it will bring all translations found
* for each record.
*
* @param \Cake\ORM\Query $query The original query to modify
* @param array $options Options
* @return \Cake\ORM\Query
*/
public function findTranslations(Query $query, array $options)
{
$locales = isset($options['locales']) ? $options['locales'] : [];
$targetAlias = $this->_translationTable->alias();
return $query->contain([$targetAlias => function ($q) use($locales, $targetAlias) {
if ($locales) {
$q->where(["{$targetAlias}.locale IN" => $locales]);
}
return $q;
}])->formatResults([$this, 'groupTranslations'], $query::PREPEND);
}
/**
* BeforeFind callback
*
* Used to add CreatedBy and ModifiedBy to the contain of the query.
*
* @param \Cake\Event\Event $event Event.
* @param \Cake\ORM\Query $query The Query object.
* @param array $options Options.
* @param bool $primary Root Query or not.
* @return void
*/
public function beforeFind($event, $query, $options, $primary)
{
if ($this->config('contain')) {
if ($this->config('created_by')) {
$query->contain(['CreatedBy' => ['fields' => $this->config('fields')]]);
}
if ($this->config('modified_by')) {
$query->contain(['ModifiedBy' => ['fields' => $this->config('fields')]]);
}
}
}
public function findAllWithOeuvresAndUsers(Query $query)
{
return $query->contain(['Oeuvres', 'Users']);
}
/**
* A finder for /admin/communities/index
*
* @param \Cake\ORM\Query $query Query
* @param array $options Options array
* @return \Cake\ORM\Query
*/
public function findAdminIndex(\Cake\ORM\Query $query, array $options)
{
$query->contain(['Clients' => function ($q) {
return $q->select(['Clients.email', 'Clients.name']);
}, 'OfficialSurvey' => function ($q) {
return $q->select(['OfficialSurvey.id', 'OfficialSurvey.sm_id', 'OfficialSurvey.alignment', 'OfficialSurvey.alignment_passed', 'OfficialSurvey.respondents_last_modified_date', 'OfficialSurvey.active']);
}, 'OrganizationSurvey' => function ($q) {
return $q->select(['OrganizationSurvey.id', 'OrganizationSurvey.sm_id', 'OrganizationSurvey.alignment', 'OrganizationSurvey.alignment_passed', 'OrganizationSurvey.respondents_last_modified_date', 'OrganizationSurvey.active']);
}, 'ParentAreas' => function ($q) {
return $q->select(['ParentAreas.name']);
}])->group('Communities.id')->select(['Communities.id', 'Communities.name', 'Communities.fast_track', 'Communities.score', 'Communities.created']);
return $query;
}
/**
* [beforeFind description]
* @param Event $event [description]
* @param Query $query [description]
* @param [type] $options [description]
* @return [type] [description]
*
* ### Options
* `images` When setting images to false nothing will be added to the query and no image fields will be returned in the resultset and will probably
* speed up overall performance
*/
public function beforeFind(Event $event, Query $query, $options = [])
{
if (isset($options['images']) && !$options['images']) {
return $query;
}
$fields = $this->config('fields');
$contain = $conditions = [];
foreach ($fields as $field => $type) {
$field = $this->_fieldName($field);
$contain[$field] = $conditions;
}
$mapper = function ($row, $key, $mapReduce) use($fields) {
foreach ($fields as $field => $type) {
$name = $this->_fieldName($field, false);
$image = isset($row[$name]) ? $row[$name] : null;
// make sure we set the correct registry alias for the entity so
// we can access the entity's repository from the ImageHelper
if (!empty($image)) {
if (is_array($image)) {
foreach ($image as &$imageEntity) {
$this->_setEntitySource($imageEntity);
}
} else {
$this->_setEntitySource($image);
}
}
if ($image === null) {
unset($row[$name]);
continue;
}
$row[$field] = $image;
unset($row[$name]);
}
if ($row instanceof Entity) {
$row->clean();
}
$mapReduce->emitIntermediate($row, $key);
};
$reducer = function ($items, $key, $mapReduce) {
if (isset($items[0])) {
$mapReduce->emit($items[0], $key);
}
};
return $query->contain($contain)->mapReduce($mapper, $reducer);
}
public function findAuth(Query $query, array $options)
{
$query->contain(['UserRoles' => ['fields' => ['UsersUserRoles.user_id', 'UserRoles.id', 'UserRoles.name', 'UserRoles.display']]])->select(['Users.id', 'Users.uuid', 'Users.username', 'Users.password']);
return $query;
}
/**
* Tests that applying array options to a query will convert them
* to equivalent function calls with the correspondent array values
*
* @return void
*/
public function testApplyOptions()
{
$options = ['fields' => ['field_a', 'field_b'], 'conditions' => ['field_a' => 1, 'field_b' => 'something'], 'limit' => 1, 'order' => ['a' => 'ASC'], 'offset' => 5, 'group' => ['field_a'], 'having' => ['field_a >' => 100], 'contain' => ['table_a' => ['table_b']], 'join' => ['table_a' => ['conditions' => ['a > b']]]];
$query = new Query($this->connection, $this->table);
$query->applyOptions($options);
$this->assertEquals(['field_a', 'field_b'], $query->clause('select'));
$expected = new QueryExpression($options['conditions'], $this->fooTypeMap);
$result = $query->clause('where');
$this->assertEquals($expected, $result);
$this->assertEquals(1, $query->clause('limit'));
$expected = new QueryExpression(['a > b']);
$expected->typeMap($this->fooTypeMap);
$result = $query->clause('join');
$this->assertEquals(['table_a' => ['alias' => 'table_a', 'type' => 'INNER', 'conditions' => $expected]], $result);
$expected = new OrderByExpression(['a' => 'ASC']);
$this->assertEquals($expected, $query->clause('order'));
$this->assertEquals(5, $query->clause('offset'));
$this->assertEquals(['field_a'], $query->clause('group'));
$expected = new QueryExpression($options['having']);
$expected->typeMap($this->fooTypeMap);
$this->assertEquals($expected, $query->clause('having'));
$expected = ['table_a' => ['table_b' => []]];
$this->assertEquals($expected, $query->contain());
}
/**
* Custom finder method used to retrieve all versions for the found records.
*
* Versioned values will be found for each entity under the property `_versions`.
*
* ### Example:
*
* {{{
* $article = $articles->find('versions')->first();
* $firstVersion = $article->get('_versions')[1];
* }}}
*
* @param \Cake\ORM\Query $query The original query to modify
* @param array $options Options
* @return \Cake\ORM\Query
*/
public function findVersions(Query $query, array $options)
{
$table = $this->_config['versionTable'];
return $query->contain([$table => function ($q) use($table, $options) {
if (!empty($options['primaryKey'])) {
$q->where(["{$table}.foreign_key IN" => $options['primaryKey']]);
}
if (!empty($options['versionId'])) {
$q->where(["{$table}.version_id IN" => $options['versionId']]);
}
$q->where(['field IN' => $this->_fields()]);
return $q;
}])->formatResults([$this, 'groupVersions'], $query::PREPEND);
}
