/**
* @param \yii\db\ActiveRecord $model
* @param string $relation
* @param \yii\db\ActiveQuery $activeRelation
* @return string
* @throws Exception
*/
protected static function getRelationValue($model, $relation, $activeRelation)
{
$foreignKeys = array_values($activeRelation->link);
$relation = Html::getAttributeName($relation);
if ($activeRelation->multiple) {
if (property_exists($model, $relation)) {
// special case for search models, where there is a relation property defined that holds the keys
$value = $model->{$relation};
} else {
/** @var \yii\db\ActiveRecord $modelClass */
$modelClass = $activeRelation->modelClass;
$value = array_map('\\netis\\crud\\crud\\Action::exportKey', $activeRelation->select($modelClass::primaryKey())->asArray()->all());
}
if (is_array($value)) {
$value = Action::implodeEscaped(Action::KEYS_SEPARATOR, $value);
}
return $value;
}
// special case for search models, where fks holds array of keys
$foreignKey = reset($foreignKeys);
if (!is_array($model->getAttribute($foreignKey))) {
return Action::exportKey($model->getAttributes($foreignKeys));
}
if (count($foreignKeys) > 1) {
throw new Exception('Composite foreign keys are not supported for searching.');
}
return Action::implodeEscaped(Action::KEYS_SEPARATOR, $model->getAttribute($foreignKey));
}
/**
* @param \yii\db\ActiveQuery $query
* @param string $attribute
* @param int $from
* @param int $size
* @param int $offset
* @param int|null $freeFrom
* @param int $freeSize
* @param int|null $targetDepth
* @param array $additional
* @return int|null
* @throws Exception
* @throws \yii\db\Exception
*/
protected function optimizeAttribute($query, $attribute, $from, $size, $offset = 0, $freeFrom = null, $freeSize = 0, $targetDepth = null, $additional = [])
{
$primaryKey = $this->getPrimaryKey();
$result = null;
$isForward = $attribute === $this->leftAttribute;
// @todo: pgsql and mssql optimization
if (in_array($this->owner->getDb()->driverName, ['mysql', 'mysqli'])) {
// mysql optimization
$tableName = $this->owner->tableName();
$additionalString = null;
$additionalParams = [];
foreach ($additional as $name => $value) {
$additionalString .= ", [[{$name}]] = ";
if ($value instanceof Expression) {
$additionalString .= $value->expression;
foreach ($value->params as $n => $v) {
$additionalParams[$n] = $v;
}
} else {
$paramName = ':nestedIntervals' . count($additionalParams);
$additionalString .= $paramName;
$additionalParams[$paramName] = $value;
}
}
$command = $query->select([$primaryKey, $attribute, $this->depthAttribute])->orderBy([$attribute => $isForward ? SORT_ASC : SORT_DESC])->createCommand();
$this->owner->getDb()->createCommand("\n UPDATE\n {$tableName} u,\n (SELECT\n [[{$primaryKey}]],\n IF (@i := @i + 1, 0, 0)\n + IF ([[{$attribute}]] " . ($isForward ? '>' : '<') . " @freeFrom,\n IF (\n (@result := @i)\n + IF (@depth - :targetDepth > 0, @result := @result + @depth - :targetDepth, 0)\n + (@i := @i + :freeSize * 2)\n + (@freeFrom := NULL), 0, 0),\n 0)\n + IF (@depth - [[{$this->depthAttribute}]] >= 0,\n IF (@i := @i + @depth - [[{$this->depthAttribute}]] + 1, 0, 0),\n 0)\n + (:from " . ($isForward ? '+' : '-') . " (CAST(@i AS UNSIGNED INTEGER) + :offset) * :size)\n + IF ([[{$attribute}]] = @freeFrom,\n IF ((@result := @i) + (@i := @i + :freeSize * 2) + (@freeFrom := NULL), 0, 0),\n 0)\n + IF (@depth := [[{$this->depthAttribute}]], 0, 0)\n as 'new'\n FROM\n (SELECT @i := 0, @depth := -1, @freeFrom := :freeFrom, @result := NULL) v,\n (" . $command->sql . ") t\n ) tmp\n SET u.[[{$attribute}]]=tmp.[[new]] {$additionalString}\n WHERE tmp.[[{$primaryKey}]]=u.[[{$primaryKey}]]")->bindValues($additionalParams)->bindValues($command->params)->bindValues([':from' => $from, ':size' => $size, ':offset' => $offset, ':freeFrom' => $freeFrom, ':freeSize' => $freeSize, ':targetDepth' => $targetDepth])->execute();
if ($freeFrom !== null) {
$result = $this->owner->getDb()->createCommand("SELECT IFNULL(@result, @i + 1 + IF (@depth - :targetDepth > 0, @depth - :targetDepth, 0))")->bindValue(':targetDepth', $targetDepth)->queryScalar();
$result = $result === null ? null : (int) $result;
}
return $result;
} else {
// generic algorithm (very slow!)
$query->select([$primaryKey, $attribute, $this->depthAttribute])->asArray()->orderBy([$attribute => $isForward ? SORT_ASC : SORT_DESC]);
$prevDepth = -1;
$i = 0;
foreach ($query->each() as $data) {
$i++;
if ($freeFrom !== null && $freeFrom !== (int) $data[$attribute] && ($freeFrom > (int) $data[$attribute] xor $isForward)) {
$result = $i;
$depthDiff = $prevDepth - $targetDepth;
if ($depthDiff > 0) {
$result += $depthDiff;
}
$i += $freeSize * 2;
$freeFrom = null;
}
$depthDiff = $prevDepth - $data[$this->depthAttribute];
if ($depthDiff >= 0) {
$i += $depthDiff + 1;
}
$this->owner->updateAll(array_merge($additional, [$attribute => $isForward ? $from + ($i + $offset) * $size : $from - ($i + $offset) * $size]), [$primaryKey => $data[$primaryKey]]);
if ($freeFrom !== null && $freeFrom === (int) $data[$attribute]) {
$result = $i;
$i += $freeSize * 2;
$freeFrom = null;
}
$prevDepth = $data[$this->depthAttribute];
}
if ($freeFrom !== null) {
$result = $i + 1;
$depthDiff = $prevDepth - $targetDepth;
if ($depthDiff > 0) {
$result += $depthDiff;
}
}
return $result;
}
}
/**
* Reestablishes links between current model and records from $relation specified by $keys.
* Removes and inserts rows into a junction table.
* @param \yii\db\ActiveQuery $relation
* @param array $keys
* @param array $removeKeys
* @throws InvalidCallException
*/
private function linkJunctionByKeys($relation, $keys, $removeKeys = null)
{
/** @var \yii\db\ActiveRecord $owner */
$owner = $this->owner;
if ($owner->getIsNewRecord()) {
throw new InvalidCallException('Unable to link model: the model cannot be newly created.');
}
/* @var $viaRelation \yii\db\ActiveQuery */
$viaRelation = is_array($relation->via) ? $relation->via[1] : $relation->via;
if (is_array($relation->via)) {
/* @var $viaClass \yii\db\ActiveRecord */
$viaClass = $viaRelation->modelClass;
$viaTable = $viaClass::getTableSchema()->fullName;
} else {
/* @var $viaTable string */
$viaTable = reset($relation->via->from);
}
$schema = $owner::getDb()->getSchema();
$this->checkAccess($relation->modelClass, array_merge($keys, $removeKeys === null ? [] : $removeKeys), 'read');
if (!empty($keys) || !empty($removeKeys)) {
$owner::getDb()->createCommand()->delete($viaTable, ['and', $removeKeys === null ? $this->buildKeyInCondition('not in', array_values($relation->link), $keys) : $this->buildKeyInCondition('in', array_values($relation->link), $removeKeys), array_combine(array_keys($viaRelation->link), $owner->getAttributes(array_values($viaRelation->link)))])->execute();
}
if (empty($keys)) {
return;
}
/** @var \yii\db\ActiveRecord $relationClass */
$relationClass = $relation->modelClass;
$quotedViaTable = $schema->quoteTableName($viaTable);
$quotedColumns = implode(', ', array_map([$schema, 'quoteColumnName'], array_merge(array_keys($viaRelation->link), array_values($relation->link))));
$prefixedPrimaryKeys = array_map(function ($c) {
return 't.' . $c;
}, array_keys($relation->link));
$prefixedForeignKeys = array_map(function ($c) {
return 'j.' . $c;
}, array_values($relation->link));
$alreadyLinked = $relation->select(array_keys($relation->link))->asArray()->all();
// a subquery is used as a more SQL portable way to specify list of values by putting them in a condition
$subquery = (new Query())->select(array_merge(array_map(function ($c) use($schema) {
return '(' . $schema->quoteValue($c) . ')';
}, $owner->getAttributes(array_values($viaRelation->link))), $prefixedPrimaryKeys))->from($relationClass::tableName() . ' t')->where($this->buildKeyInCondition('in', $prefixedPrimaryKeys, $keys))->andWhere($this->buildKeyInCondition('not in', $prefixedPrimaryKeys, $alreadyLinked));
if ($removeKeys === null) {
$subquery->leftJoin($viaTable . ' j', array_merge(array_combine(array_map(function ($c) {
return 'j.' . $c;
}, array_keys($viaRelation->link)), $owner->getAttributes(array_values($viaRelation->link))), array_combine($prefixedForeignKeys, array_map(function ($k) {
return new Expression($k);
}, $prefixedPrimaryKeys))));
$subquery->andWhere(array_fill_keys($prefixedForeignKeys, null));
}
list($subquery, $params) = $owner::getDb()->getQueryBuilder()->build($subquery);
$query = "INSERT INTO {$quotedViaTable} ({$quotedColumns}) {$subquery}";
$owner::getDb()->createCommand($query, $params)->execute();
}
/**
* 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;
}
