This method allows you to reuse existing relation definitions to perform JOIN queries.
Based on the definition of the specified relation(s), the method will append one or multiple
JOIN statements to the current query.
If the $eagerLoading parameter is true, the method will also perform eager loading for the specified relations,
which is equivalent to calling [[with()]] using the specified relations.
Note that because a JOIN query will be performed, you are responsible to disambiguate column names.
This method differs from [[with()]] in that it will build up and execute a JOIN SQL statement
for the primary table. And when $eagerLoading is true, it will call [[with()]] in addition with the specified relations.
| public joinWith ( string | array $with, boolean | array $eagerLoading = true, string | array $joinType = 'LEFT JOIN' ) | ||
| $with | string | array | the relations to be joined. This can either be a string, representing a relation name or an array with the following semantics: - Each array element represents a single relation. - You may specify the relation name as the array key and provide an anonymous functions that can be used to modify the relation queries on-the-fly as the array value. - If a relation query does not need modification, you may use the relation name as the array value. The relation name may optionally contain an alias for the relation table (e.g. `books b`). Sub-relations can also be specified, see [[with()]] for the syntax. In the following you find some examples: ```php // find all orders that contain books, and eager loading "books" Order::find()->joinWith('books', true, 'INNER JOIN')->all(); // find all orders, eager loading "books", and sort the orders and books by the book names. Order::find()->joinWith([ 'books' => function (\yii\db\ActiveQuery $query) { $query->orderBy('item.name'); } ])->all(); // find all orders that contain books of the category 'Science fiction', using the alias "b" for the books table Order::find()->joinWith(['books b'], true, 'INNER JOIN')->where(['b.category' => 'Science fiction'])->all(); ``` The alias syntax is available since version 2.0.7. |
| $eagerLoading | boolean | array | whether to eager load the relations specified in `$with`. When this is a boolean, it applies to all relations specified in `$with`. Use an array to explicitly list which relations in `$with` need to be eagerly loaded. Defaults to `true`. |
| $joinType | string | array | the join type of the relations specified in `$with`. When this is a string, it applies to all relations specified in `$with`. Use an array in the format of `relationName => joinType` to specify different join types for different relations. |
public function setupCriteria()
{
$this->activeQuery->joinWith('content');
$this->activeQuery->joinWith('content.createdBy');
$this->activeQuery->joinWith('content.contentContainer');
$this->activeQuery->limit($this->limit);
$this->activeQuery->andWhere(['user.status' => User::STATUS_ENABLED]);
/**
* Handle Stream Mode (Normal Stream or Activity Stream)
*/
if ($this->mode == self::MODE_ACTIVITY) {
$this->activeQuery->andWhere(['content.object_model' => \humhub\modules\activity\models\Activity::className()]);
// Dont show own activities
if ($this->user !== null) {
$this->activeQuery->leftJoin('activity', 'content.object_id=activity.id AND content.object_model=:activityModel', ['activityModel' => \humhub\modules\activity\models\Activity::className()]);
$this->activeQuery->andWhere('content.created_by != :userId', array(':userId' => $this->user->id));
}
} else {
$this->activeQuery->andWhere(['!=', 'content.object_model', \humhub\modules\activity\models\Activity::className()]);
}
/**
* Setup Sorting
*/
if ($this->sort == self::SORT_UPDATED_AT) {
$this->activeQuery->orderBy('wall_entry.updated_at DESC');
if ($this->from != "") {
$this->activeQuery->andWhere("wall_entry.updated_at < (SELECT updated_at FROM wall_entry wd WHERE wd.id=" . $this->from . ")");
}
} else {
$this->activeQuery->orderBy('wall_entry.id DESC');
if ($this->from != "") {
$this->activeQuery->andWhere("wall_entry.id < " . $this->from);
}
}
}
public function relation($relation_name)
{
$relation = ModelHelper::getRelation($this->model, $relation_name);
$relationClass = $relation->modelClass;
$relationField = $relationClass::tableName() . '.' . ($relation->multiple ? array_values($relation->link)[0] : array_keys($relation->link)[0]);
$relationValue = $this->model->{$relation_name};
if ($relationValue instanceof ActiveRecord) {
$relationValue = ModelHelper::getPkColumnValue($relationValue);
}
$this->query->joinWith($relation_name)->andFilterWhere([$relationField => $relationValue]);
}
/**
* @param \yii\db\ActiveQuery $query
* @param string $field
* @return bool
* @throws BadRequestHttpException
*/
protected function prepareRelationField($query, &$field)
{
if (strpos($field, '.') === false) {
return false;
}
$model = $this->model;
$fullRelation = '';
$fieldElements = explode('.', $field);
$fieldElementsCount = count($fieldElements);
for ($i = 1; $i < $fieldElementsCount; ++$i) {
$relationName = $fieldElements[$i - 1];
$relationMethod = 'get' . ucfirst($relationName);
if (!method_exists($model, $relationMethod)) {
throw new BadRequestHttpException('Relation isn\'t exist.');
}
/** @var \yii\db\ActiveQuery $relationQuery */
$relationQuery = $model->{$relationMethod}();
/** @var \yii\db\ActiveRecord $relationModel */
$model = new $relationQuery->modelClass();
$fullRelation .= '.' . $relationName;
}
$query->joinWith(trim($fullRelation, '.'));
$attribute = $fieldElements[$fieldElementsCount - 1];
if (!$model->isAttributeSafe($attribute)) {
throw new BadRequestHttpException('Unsafe relation attribute.');
}
$field = $model::tableName() . '.' . $attribute;
return true;
}
/**
* Конфигурирование объекта запроса поиска по элементам.
*
* @param \yii\db\ActiveQuery $activeQuery
* @param null $modelClassName
* @return $this
*/
public function buildElementsQuery(\yii\db\ActiveQuery $activeQuery)
{
$where = [];
//Нужно учитывать связанные дополнительные данные
if ($this->enabledElementProperties == Cms::BOOL_Y) {
$activeQuery->joinWith('cmsContentElementProperties');
//Нужно учитывать настройки связанные дополнительные данных
if ($this->enabledElementPropertiesSearchable == Cms::BOOL_Y) {
$activeQuery->joinWith('cmsContentElementProperties.property');
$where[] = ['and', ['like', CmsContentElementProperty::tableName() . ".value", '%' . $this->searchQuery . '%', false], [CmsContentProperty::tableName() . ".searchable" => Cms::BOOL_Y]];
} else {
$where[] = ['like', CmsContentElementProperty::tableName() . ".value", '%' . $this->searchQuery . '%', false];
}
}
//Поиск по основному набору полей
if ($this->searchElementFields) {
foreach ($this->searchElementFields as $fieldName) {
$where[] = ['like', CmsContentElement::tableName() . "." . $fieldName, '%' . $this->searchQuery . '%', false];
}
}
if ($where) {
$where = array_merge(['or'], $where);
$activeQuery->andWhere($where);
}
//Отфильтровать только конкретный тип
if ($this->searchElementContentIds) {
$activeQuery->andWhere([CmsContentElement::tableName() . ".content_id" => (array) $this->searchElementContentIds]);
}
return $this;
}
/**
* Adds a condition to search in relations using subquery.
* @todo this should be called for each token, to group their conditions with OR and group token groups with AND
*
* @param \yii\db\ActiveQuery $query
* @param array $tokens all search tokens extracted from term
* @param array $relationAttributes array of string(relation name) => array(
* 'model' => netis\crud\db\ActiveRecord,
* 'searchModel' => netis\crud\db\ActiveSearchTrait,
* 'attributes' => array
* )
* @return array conditions to add to $query
*/
protected function processSearchRelated(\yii\db\ActiveQuery $query, array $tokens, array $relationAttributes)
{
$allConditions = ['or'];
foreach ($relationAttributes as $relationName => $relation) {
/**
* @todo optimize this (check first, don't want to loose another battle with PostgreSQL query planner):
* - for BELONGS_TO check fk against subquery
* - for HAS_MANY and HAS_ONE check pk against subquery
* - for MANY_MANY join only to pivot table and check its fk agains subquery
*/
$query->joinWith([$relationName => function ($query) use($relationName) {
/** @var \yii\db\ActiveQuery $query */
/** @var \yii\db\ActiveRecord $class */
$class = $query->modelClass;
return $query->select(false)->from([$relationName => $class::tableName()]);
}]);
$conditions = ['and'];
/** @var ActiveSearchInterface $searchModel */
$searchModel = $relation['searchModel'];
if (!$searchModel instanceof ActiveSearchInterface) {
continue;
}
foreach ($tokens as $token) {
$condition = $searchModel->processSearchToken($token, $relation['attributes'], $relationName);
if ($condition !== null) {
$conditions[] = $condition;
}
}
if ($conditions !== ['and']) {
$allConditions[] = $conditions;
}
}
return $allConditions !== ['or'] ? $allConditions : null;
}
