protected function rebuildLftRgt(string $table)
    {
        $builder = QueryBuilderFactory::getQueryBuilder($this->getConnection());

        $subQuerySql = (clone $builder)
            ->clear()
            ->select('tt.parent_id')
            ->table($table, 'tt')
            ->where('tt.parent_id=t.id')
            ->toSQL();

        $sql = (clone $builder)
            ->clear()
            ->table($table, 't')
            ->select('t.id, t.root, t.lft, t.rgt, t.rgt-t.lft-1 AS move')
            ->where('NOT t.lft=(t.rgt-1) AND NOT t.id IN ('.$subQuerySql.')')
            ->order('rgt ASC')
            ->toSQL();

        $rows = $this
            ->getConnection()
            ->query($sql)
            ->fetchAll();

        foreach ($rows as $row) {
            /*
            $sql = strtr(
                'UPDATE {table} SET lft=lft-{move}, rgt=rgt-{move} WHERE root={root} AND lft>{rgt}',
                [
                    '{table}' => $table,
                    '{move}' => $row['move'],
                    '{root}' => $row['root'],
                    '{lft}' => $row['rgt'],
                ]
            );
            */
            $sql = (clone $builder)
                ->clear()
                ->update()
                ->table($table)
                ->values([
                    'lft' => new Expression(sprintf('lft-%s', $row['move'])),
                    'rgt' => new Expression(sprintf('rgt-%s', $row['move'])),
                ])
                ->where([
                    'root' => $row['root'],
                    'lft__gt' => $row['rgt'],
                ])
                ->toSQL();

            $this
                ->getConnection()
                ->query($sql)
                ->execute();

            /*
            $sql = strtr(
                'UPDATE {table} SET rgt=rgt-{move} WHERE root={root} AND lft<{rgt} AND rgt>={rgt}',
                [
                    '{table}' => $table,
                    '{move}' => $row['move'],
                    '{root}' => $row['root'],
                    '{lft}' => $row['rgt'],
                ]
            );
            */
            $sql = (clone $builder)
                ->clear()
                ->update()
                ->table($table)
                ->values([
                    'rgt' => new Expression(sprintf('rgt-%s', $row['move'])),
                ])
                ->where([
                    'root' => $row['root'],
                    'lft__lt' => $row['rgt'],
                    'rgt__gte' => $row['rgt'],
                ])
                ->toSQL();

            $this
                ->getConnection()
                ->query($sql)
                ->execute();
        }

        return $this;
    }
protected function rebuildLftRgt(string $table)
    {
        $builder = QueryBuilderFactory::getQueryBuilder($this->getConnection());

        $subQuerySql = (clone $builder)
            ->clear()
            ->select('tt.parent_id')
            ->table($table, 'tt')
            ->where('tt.parent_id=t.id')
            ->toSQL();

        $sql = (clone $builder)
            ->clear()
            ->table($table, 't')
            ->select('t.id, t.root, t.lft, t.rgt, t.rgt-t.lft-1 AS move')
            ->where('NOT t.lft=(t.rgt-1) AND NOT t.id IN ('.$subQuerySql.')')
            ->order('rgt ASC')
            ->toSQL();

        $rows = $this
            ->getConnection()
            ->query($sql)
            ->fetchAll();

        foreach ($rows as $row) {
            /*
            $sql = strtr(
                'UPDATE {table} SET lft=lft-{move}, rgt=rgt-{move} WHERE root={root} AND lft>{rgt}',
                [
                    '{table}' => $table,
                    '{move}' => $row['move'],
                    '{root}' => $row['root'],
                    '{lft}' => $row['rgt'],
                ]
            );
            */
            $sql = (clone $builder)
                ->clear()
                ->update()
                ->table($table)
                ->values([
                    'lft' => new Expression(sprintf('lft-%s', $row['move'])),
                    'rgt' => new Expression(sprintf('rgt-%s', $row['move'])),
                ])
                ->where([
                    'root' => $row['root'],
                    'lft__gt' => $row['rgt'],
                ])
                ->toSQL();

            $this
                ->getConnection()
                ->query($sql)
                ->execute();

            /*
            $sql = strtr(
                'UPDATE {table} SET rgt=rgt-{move} WHERE root={root} AND lft<{rgt} AND rgt>={rgt}',
                [
                    '{table}' => $table,
                    '{move}' => $row['move'],
                    '{root}' => $row['root'],
                    '{lft}' => $row['rgt'],
                ]
            );
            */
            $sql = (clone $builder)
                ->clear()
                ->update()
                ->table($table)
                ->values([
                    'rgt' => new Expression(sprintf('rgt-%s', $row['move'])),
                ])
                ->where([
                    'root' => $row['root'],
                    'lft__lt' => $row['rgt'],
                    'rgt__gte' => $row['rgt'],
                ])
                ->toSQL();

            $this
                ->getConnection()
                ->query($sql)
                ->execute();
        }

        return $this;
    }
protected function rebuildLftRgt ( string $ table ) { $ builder = QueryBuilderFactory :: getQueryBuilder ( $ this -> getConnection ( ) ) ; $ subQuerySql = ( clone $ builder ) -> clear ( ) -> select ( 'tt.parent_id' ) -> table ( $ table , 'tt' ) -> where ( 'tt.parent_id=t.id' ) -> toSQL ( ) ; $ sql = ( clone $ builder ) -> clear ( ) -> table ( $ table , 't' ) -> select ( 't.id, t.root, t.lft, t.rgt, t.rgt-t.lft-1 AS move' ) -> where ( 'NOT t.lft=(t.rgt-1) AND NOT t.id IN (' . $ subQuerySql . ')' ) -> order ( 'rgt ASC' ) -> toSQL ( ) ; $ rows = $ this -> getConnection ( ) -> query ( $ sql ) -> fetchAll ( ) ; foreach ( $ rows as $ row ) { /*
            $sql = strtr(
                'UPDATE {table} SET lft=lft-{move}, rgt=rgt-{move} WHERE root={root} AND lft>{rgt}',
                [
                    '{table}' => $table,
                    '{move}' => $row['move'],
                    '{root}' => $row['root'],
                    '{lft}' => $row['rgt'],
                ]
            );
            */ $ sql = ( clone $ builder ) -> clear ( ) -> update ( ) -> table ( $ table ) -> values ( [ 'lft' => new Expression ( sprintf ( 'lft-%s' , $ row [ 'move' ] ) ) , 'rgt' => new Expression ( sprintf ( 'rgt-%s' , $ row [ 'move' ] ) ) , ] ) -> where ( [ 'root' => $ row [ 'root' ] , 'lft__gt' => $ row [ 'rgt' ] , ] ) -> toSQL ( ) ; $ this -> getConnection ( ) -> query ( $ sql ) -> execute ( ) ; /*
            $sql = strtr(
                'UPDATE {table} SET rgt=rgt-{move} WHERE root={root} AND lft<{rgt} AND rgt>={rgt}',
                [
                    '{table}' => $table,
                    '{move}' => $row['move'],
                    '{root}' => $row['root'],
                    '{lft}' => $row['rgt'],
                ]
            );
            */ $ sql = ( clone $ builder ) -> clear ( ) -> update ( ) -> table ( $ table ) -> values ( [ 'rgt' => new Expression ( sprintf ( 'rgt-%s' , $ row [ 'move' ] ) ) , ] ) -> where ( [ 'root' => $ row [ 'root' ] , 'lft__lt' => $ row [ 'rgt' ] , 'rgt__gte' => $ row [ 'rgt' ] , ] ) -> toSQL ( ) ; $ this -> getConnection ( ) -> query ( $ sql ) -> execute ( ) ; } return $ this ; }
Find and delete broken branches without root, parent
and with incorrect lft, rgt.

sql:
SELECT id, root, lft, rgt, (rgt-lft-1) AS move
FROM tbl t
WHERE NOT t.lft = (t.rgt-1)
AND NOT id IN (
SELECT tc.parent_id
FROM tbl tc
WHERE tc.parent_id = t.id
)
ORDER BY rgt DESC

@param $table

@throws \Doctrine\DBAL\DBALException
@throws \Mindy\QueryBuilder\Exception\NotSupportedException

@return TreeQuerySet
Find and delete broken branches without root parent and with incorrect lft rgt .