test.php

<?php
$subSelect = new Select();

$subSelect
    ->from(['head_parent' => $this->config->getTable()])
    ->columns([])
    ->join(
        ['parent' => $this->config->getTable()],
        new Expression(
            '?.? >= ?.? AND ?.? < ?.?',
            [
                ['parent' => Expression::TYPE_IDENTIFIER],
                [$this->config->getLeftColumn() => Expression::TYPE_IDENTIFIER],
                ['head_parent' => Expression::TYPE_IDENTIFIER],
                [$this->config->getLeftColumn() => Expression::TYPE_IDENTIFIER],
                ['parent' => Expression::TYPE_IDENTIFIER],
                [$this->config->getRightColumn() => Expression::TYPE_IDENTIFIER],
                ['head_parent' => Expression::TYPE_IDENTIFIER],
                [$this->config->getRightColumn() => Expression::TYPE_IDENTIFIER]
            ]
        ),
        []
    )
    ->join(
        ['child' => $this->config->getTable()],
        new Expression(
            '?.? BETWEEN ?.? AND ?.?',
            [
                ['child' => Expression::TYPE_IDENTIFIER],
                [$this->config->getLeftColumn() => Expression::TYPE_IDENTIFIER],
                ['parent' => Expression::TYPE_IDENTIFIER],
                [$this->config->getLeftColumn() => Expression::TYPE_IDENTIFIER],
                ['parent' => Expression::TYPE_IDENTIFIER],
                [$this->config->getRightColumn() => Expression::TYPE_IDENTIFIER]
            ]
        ),
        [
            'id' => new Expression(
                '?.?',
                [
                    ['child' => Expression::TYPE_IDENTIFIER],
                    [$this->config->getIdColumn() => Expression::TYPE_IDENTIFIER]
                ]
            ),
            'depth' => new Expression(
                '(CASE WHEN ?.? = :childDepthId THEN 0 ELSE COUNT(*) END)',
                [
                    ['child' => Expression::TYPE_IDENTIFIER],
                    [$this->config->getIdColumn() => Expression::TYPE_IDENTIFIER]
                ]
            )
        ]
    )
    ->group(
        new Expression(
            '?.?',
            [
                ['child' => Expression::TYPE_IDENTIFIER],
                [$this->config->getIdColumn() => Expression::TYPE_IDENTIFIER]
            ]
        )
    );

$subSelect->having
    ->greaterThanOrEqualTo(new Expression('COUNT(*)'), 1);

if ($this->depthLimit) {
    $subSelect->having
        ->lessThanOrEqualTo(new Expression('COUNT(*)'), $this->depthLimit);
}

$subSelect->where
    ->equalTo(
        new Expression(
            '?.?',
            [
                ['head_parent' => Expression::TYPE_IDENTIFIER],
                [$this->config->getIdColumn() => Expression::TYPE_IDENTIFIER]
            ]
        ),
        new Expression(':id')
    )
    ->greaterThan(
        new Expression(
            '?.?',
            [
                ['parent' => Expression::TYPE_IDENTIFIER],
                [$this->config->getIdColumn() => Expression::TYPE_IDENTIFIER]
            ]
        ),
        $this->config->getRootNodeId()
    );

if ($this->includeSearchingNode) {
    $subSelect
        ->where
        ->or
        ->equalTo(
            new Expression(
                '?.?',
                [
                    ['child' => Expression::TYPE_IDENTIFIER],
                    [$this->config->getIdColumn() => Expression::TYPE_IDENTIFIER]
                ]
            ),
            new Expression(':searchNodeId'));
}

$select = new Select();

$select
    ->from(['q' => $subSelect])
    ->columns(['depth'])
    ->join(
        ['t' => $this->config->getTable()],
        new Expression(
            '?.? = ?.?',
            [
                ['t' => Expression::TYPE_IDENTIFIER],
                [$this->config->getIdColumn() => Expression::TYPE_IDENTIFIER],
                ['q' => Expression::TYPE_IDENTIFIER],
                [$this->config->getIdColumn() => Expression::TYPE_IDENTIFIER]
            ]
        ),
        $this->columns
    )
    ->order(new Expression(
            '?.? ASC',
            [
                ['t' => Expression::TYPE_IDENTIFIER],
                [$this->config->getLeftColumn() => Expression::TYPE_IDENTIFIER]
            ]
        )
    );

$statement = $this->sql->prepareStatementForSqlObject($select);

$parameters = [
    ':id' => $id,
    ':childDepthId' => $id
];

if ($this->includeSearchingNode) {
    $parameters[':searchNodeId'] = $id;
}

$result = $this->statement->execute($parameters);