/** * @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; } }