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