Trait to manage a nested set in Joomla!3

NestedSet.php

<?php
namespace Traits;

use Joomla\CMS\Factory;
use Throwable;
use Exceptions\NestedSetNodeNotFoundException;
use Exceptions\ParentNotDefinedException;
use Exceptions\UnexpectedException;
use NestedSetPointer;

defined('_JEXEC') or die('Restricted access');

/**
 * Provides operations to manage a nested set structure
 * on the class that uses it.
 *
 * We are assuming that the underliying table has the following fields:
 *
 * id        INT AUTO_INCREMENT PRIMARY_KEY
 * parent_id INT
 * lft       INT
 * rgt       INT
 *
 * Read a little about the nested set model.
 * @link http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
 * @link https://www.mysqltutorial.org/mysql-adjacency-list-tree/
 * @link https://www.waitingforcode.com/mysql/managing-hierarchical-data-in-mysql-nested-set/read
 *
 * on table initialization (if data exists) we need one row with parent_id null (this is the root node!)
 */
trait NestedSet
{
    /**
     * The class should tell us which table defines the nested set
     * MUST BE OVERWRITTEN!
     *
     * @var string
     * @since 2.6.25
     */
    public string $table_name = 'the_table_to_use';

    /**
     * Introduces the basic query conditions
     *
     * Parent items alias is parent
     * Children items alias is child
     *
     * @since 2.6.25
     */
    public function GetBaseQuery(string $fields = ''): \JDatabaseQuery
    {
        $db = Factory::getDbo();
        $bq = $db->getQuery(true);

        if (!empty($fields))
        {
            if (!str_contains(mb_strtoupper($fields), 'DISTINCT '))
            {
                $fields = 'DISTINCT ' . $fields;
            }

            if (!str_contains(mb_strtoupper($fields), 'child.lft'))
            {
                $fields .= ',child.lft';
            }

            $bq->select($fields);
        }

        $bq->from($db->quoteName($this->table_name) . ' as child')
            ->from($db->quoteName($this->table_name) . ' as parent')
            ->where('child.lft BETWEEN parent.lft AND parent.rgt')
            ->order('child.lft');

        return $bq;
    }

    /**
     * Returns the full tree
     *
     * @return \JDatabaseQuery
     *
     * @since 2.6.25
     */
    public function GetFullTree(): \JDatabaseQuery
    {
        return $this->GetBaseQuery()
            ->where('parent.parent_id IS NULL');
    }

    /**
     * Returns a subtree
     *
     * @param int $parent_id The parent node
     *
     * @return \JDatabaseQuery
     *
     * @since 2.6.25
     */
    public function GetSubTree(int $parent_id): \JDatabaseQuery
    {
        return $this->GetBaseQuery()
            ->where('parent.id = ' . $parent_id);
    }

    /**
     * Returs the three width node depth
     *
     * @param $fields
     *
     * @return \JDatabaseQuery
     *
     * @since 2.6.25
     */
    public function GetBaseQueryWithDepth($fields = ''): \JDatabaseQuery
    {
        $group = '';
        if (!empty($fields))
        {
            $group = $fields . (str_contains($fields, 'child.id') ? '' : 'child.id');
        }

        $q = $this->GetBaseQuery($fields);
        $q->select('count(parent.id) - 1 as depth')
            ->group($group);

        return $q;
    }

    /**
     * Gets the leafs of the nested set
     *
     * @return \JDatabaseQuery
     *
     * @since 2.6.25
     */
    public function GetLeafs(): \JDatabaseQuery
    {
        $db = Factory::getDbo();

        return $db->getQuery()
            ->from($this->table_name . ' as node')
            ->where('node.rgt = node.lft + 1');
    }

    /**
     * Gets the path to a node
     *
     * @param int $node_id
     *
     * @return \JDatabaseQuery
     *
     * @since 2.6.25
     */
    public function GetPath(int $node_id): \JDatabaseQuery
    {
        return $this->GetBaseQuery()
            ->select('parent.id')
            ->where('child.id = ' . $node_id);
    }

    /**
     * Gets the node nested set relevant data
     *
     * @param int $node_id
     *
     * @return NestedSetPointer
     *
     * @since version
     */
    public function GetNestedSetData(int $node_id)
    {
        $db = Factory::getDbo();
        $q = $db->getQuery(true)
            ->clear()
            ->select('rgt, lft, rgt-lft+1 as width, parent_id, id')
            ->from($this->table_name)
            ->where('id = ' . $node_id);

        return $db->setQuery($q)->loadObject('WasteManagement\RawData\NestedSetPointer');
    }

    /**
     * Adds the current item into the nested set
     *
     * @param object $objToSave Object to save
     *
     * @throws ParentNotDefinedException
     * @throws UnexpectedException
     *
     * @since 2.6.25
     */
    public function InsertItem(object $objToSave)
    {
        if (!isset($objToSave->parent_id))
        {
            throw new ParentNotDefinedException();
        }

        $db = Factory::getDbo();
        $lftAndRight = $this->GetNestedSetData($objToSave->parent_id);

        if ($lftAndRight) {
            $db->transactionStart();
            $parent = $lftAndRight->rgt;

            try {
                $q = "UPDATE {$this->table_name}
                    SET lft = CASE WHEN lft > $parent THEN lft + 2
                                ELSE lft END,
                        rgt = CASE WHEN rgt >= $parent THEN rgt + 2
                                ELSE rgt END
                    WHERE rgt >= $parent
                ";
                $db->setQuery($q)->execute();

                $objToSave->lft = $parent;
                $objToSave->rgt = $parent + 1;

                $db->insertObject($this->table_name, $objToSave, 'id');

                $db->transactionCommit();
            } catch (\Throwable $t) {
                $db->transactionRollback();
                throw new UnexpectedException($t->getMessage());
            }
        }
    }

    /**
     * Deletes the node its subtree
     *
     * @param int $node_id The node to delete
     *
     * @throws UnexpectedException
     * @throws NestedSetNodeNotFoundException
     */
    public function DeleteSubTree(int $node_id)
    {
        if ($nestedSetData = $this->GetNestedSetData($node_id))
        {
            $db = Factory::getDbo();
            $db->transactionStart();

            try
            {
                $q = $db->getQuery()
                    ->delete($this->table_name)
                    ->where('lft BETWEEN ' . $nestedSetData->lft . ' AND ' . $nestedSetData->rgt);
                $db->setQuery($q)->execute();
                $q->clear()
                    ->update($this->table_name)
                    ->set('rgt = rgt - ' . $nestedSetData->width)
                    ->where('rgt > ' . $nestedSetData->rgt);
                $db->setQuery($q)->execute();
                $q->clear()
                    ->update($this->table_name)
                    ->set('lft = lft - ' . $nestedSetData->width)
                    ->where('lft > ' . $nestedSetData->rgt);
                $db->setQuery($q)->execute();
                $db->transactionCommit();

                return;
            }
            catch (Throwable $t)
            {
                $db->transactionRollback();
                throw new UnexpectedException('The operation raised an error: ' . $t->getMessage());
            }
        }

        throw new NestedSetNodeNotFoundException($node_id);
    }

    /**
     * Removes only the given node
     *
     * @param int $node_id
     *
     * @throws NestedSetNodeNotFoundException
     * @throws UnexpectedException
     *
     * @since 2.6.25
     */
    public function DeleteNode(int $node_id)
    {
        if ($nestedSetData = $this->GetNestedSetData($node_id))
        {
            $db = Factory::getDbo();
            $db->transactionStart();

            try {
                $q = $db->getQuery()
                    ->delete($this->table_name)
                    ->where('lft = ' . $nestedSetData->lft);
                $db->setQuery($q)->execute();
                $q->clear()
                    ->update($this->table_name)
                    ->set('rgt = rgt -1, lft = lft - 1')
                    ->where('lft BETWEEN ' . $nestedSetData->lft . ' AND ' . $nestedSetData->rgt);
                $db->setQuery($q)->execute();
                $q->clear()
                    ->update($this->table_name)
                    ->set('rgt = rgt - 2')
                    ->where('rgt > ' . $nestedSetData->rgt);
                $db->setQuery($q)->execute();
                $q->clear()
                    ->update($this->table_name)
                    ->set('lft = lft - 2')
                    ->where('lft > ' . $nestedSetData->rgt);
                $db->setQuery($q)->execute();

                return;
            }
            catch (Throwable $t)
            {
                $db->transactionRollback();
                throw new UnexpectedException('The operation raised an error: ' . $t->getMessage());
            }
        }

        throw new NestedSetNodeNotFoundException($node_id);
    }

    /**
     * Changes the parent of a node
     *
     * @param int $node_id
     * @param int $to_parent_id
     *
     * @return mixed
     *
     * @throws NestedSetNodeNotFoundException
     * @throws UnexpectedException
     *
     * @see Joe Celko's Trees and hierarchies in SQL for smarties, 4.8.2
     *
     * @since 2.6.25
     */
    public function ChangeParent(int $node_id, int $to_parent_id)
    {
        $db = Factory::getDbo();
        $nodeData = $this->GetNestedSetData($node_id);
        $parentData = $this->GetNestedSetData($to_parent_id);

        if ($nodeData && $parentData)
        {
            $db->transactionStart();

            $originLft = $nodeData->lft;
            $originRgt = $nodeData->rgt;
            $newParentRgt = $parentData->rgt;

            $q = "UPDATE {$this->table_name}
                    SET lft = lft +
                              CASE WHEN $newParentRgt < $originLft THEN
                                CASE WHEN lft BETWEEN $originLft AND $originRgt THEN
                                    $newParentRgt - $originLft
                                WHEN lft BETWEEN $newParentRgt AND $originLft - 1 THEN
                                    $originRgt - $originLft + 1
                                ELSE 0
                                END
                              WHEN $newParentRgt > $originRgt THEN
                                CASE WHEN lft BETWEEN $originLft AND $originRgt THEN
                                    $newParentRgt - $originRgt - 1
                                WHEN lft BETWEEN $originRgt + 1 AND $newParentRgt - 1 THEN
                                    $originLft - $originRgt - 1
                                ELSE 0
                                END
                              ELSE 0
                              END,
                        rgt = rgt +
                              CASE WHEN $newParentRgt < $originLft THEN
                                CASE WHEN rgt BETWEEN $originLft AND $originRgt THEN
                                    $newParentRgt - $originLft
                                WHEN rgt BETWEEN $newParentRgt AND $originLft - 1 THEN
                                    $originRgt - $originLft + 1
                                ELSE 0
                                END
                              WHEN $newParentRgt > $originRgt THEN
                                CASE WHEN rgt BETWEEN $originLft AND $originRgt THEN
                                    $newParentRgt - $originRgt - 1
                                WHEN rgt BETWEEN $originRgt + 1 AND $newParentRgt - 1 THEN
                                    $originLft - $originRgt - 1
                                ELSE 0
                                END
                              ELSE 0
                              END
            ";
            $db->setQuery($q)->execute();

            $q = "UPDATE {$this->table_name}
                    SET parent_id = {$parentData->id}
                    WHERE id = {$nodeData->id}
            ";
            $db->setQuery($q)->execute();

            $db->transactionCommit();
        }
        else
        {
            throw new NestedSetNodeNotFoundException($nodeData ? $to_parent_id : $node_id);
        }
    }
}

NestedSetPointer.php

namespace RawData;

class NestedSetPointer
{
    public ?int $lft;
    public ?int $rgt;
    public ?int $width;
    public ?int $parent_id;
    public ?int $id;
}