Closure Table Trait for Laravel Eloquent Models

ClosureTable.php

<?php

namespace App\Models\Traits;

use Illuminate\Support\Facades\DB;

/**
 * This trait keeps functionality to read & write hierarchical data from & into a closure table.
 * IMPORTANT: In your model please set the variable $closure_table, e.g.
 * protected $closure_table = 'destinations_tree';
 */
trait ClosureTable
{
    /**
     * Returns all "older" relatives which are above.
     *
     * @return Illuminate\Database\Eloquent\Relations\BelongsToMany
     */
    public function ancestors()
    {
        return $this->upwards()->where('ancestor', '!=', $this->id);
    }

    /**
     * Returns the "oldest" relevant which is a tree's starting point.
     *
     * @return Illuminate\Database\Eloquent\Model
     */
    public function root()
    {
        return $this->ancestors()->orderBy('depth', 'desc')->first();
    }

    /**
     * Returns all "younger" relevants which are underneath.
     *
     * @return Illuminate\Database\Eloquent\Relations\BelongsToMany
     */
    public function descendants()
    {
        return $this->downwards()->where('descendant', '!=', $this->id);
    }

    /**
     * Returns all upwards elements of a tree ("thicker branches") with the beginning node itself.
     *
     * @return Illuminate\Database\Eloquent\Relations\BelongsToMany
     */
    public function upwards()
    {
        $query = $this->belongsToMany(
                            get_called_class(),
                            $this->closure_table,
                            'descendant',
                            'ancestor'
                        )
                        ->orderBy('depth', 'asc');

        return $query;
    }

    /**
     * Returns all downwards elements of a tree ("thinner branches") with the beginning node itself.
     *
     * @return Illuminate\Database\Eloquent\Relations\BelongsToMany
     */
    public function downwards()
    {
        $query = $this->belongsToMany(
                            get_called_class(),
                            $this->closure_table,
                            'ancestor',
                            'descendant'
                        )
                        ->orderBy('depth', 'asc');

        return $query;
    }

    /**
     * Adds a "0"-depth row for a root entity.
     *
     * @return void
     */
    public function makeRoot()
    {
        DB::table($this->closure_table)->insert(
            [
                'ancestor' => $this->id,
                'descendant' => $this->id,
                'depth' => 0,
            ]
        );

    }

    /**
     * Decides whether to add a new parent node or move from an existing to another one.
     *
     * @param int $parent_id
     * @return void
     */
    public function setParent(int $parent_id)
    {
        if ($this->hasExistingParent()) {
            $this->moveToParent($parent_id);
            return;
        }

        $this->addToParent($parent_id);
    }

    /**
     * Adds a new entity to the tree underneath a parent node.
     *
     * @param int $parent_id
     * @return void
     */
    protected function addToParent(int $parent_id)
    {
        $this->checkParentValidity($parent_id);

        $sql = "INSERT INTO {$this->closure_table} (ancestor, descendant, depth)
                    SELECT t.ancestor, CAST(:object_id AS INTEGER), t.depth+1
                    FROM {$this->closure_table} AS t
                    WHERE t.descendant = :parent_id
                    UNION ALL
                    SELECT CAST(:object_id AS INTEGER), CAST(:object_id AS INTEGER), 0";

        DB::connection($this->connection)->statement($sql, [
            'object_id' => $this->id,
            'parent_id' => $parent_id,
        ]);
    }

    /**
     * Moves an existing node and its descendants to a different parent.
     *
     * @param int $parent_id
     * @return void
     */
    protected function moveToParent(int $parent_id)
    {
        $this->checkParentValidity($parent_id);

        // Delete all existing ancestor associations for the current entity
        // and delete all associations between the current entity's children and ancestors above the current entity
        $delete_sql = "DELETE FROM {$this->closure_table}
            WHERE descendant IN (
              SELECT d FROM (
                SELECT descendant as d FROM {$this->closure_table}
                WHERE ancestor = :object_id
              ) as dct
            )
            AND ancestor IN (
              SELECT a FROM (
                SELECT ancestor AS a FROM {$this->closure_table}
                WHERE descendant = :object_id
                AND ancestor <> :object_id
              ) as ct
            )";

        // Write the associations for the new ancestors for the current entity and all its children
        $insert_sql = "INSERT INTO {$this->closure_table} (ancestor, descendant, depth)
                    SELECT supertree.ancestor, subtree.descendant, supertree.depth+subtree.depth+1
                    FROM {$this->closure_table} AS supertree
                    CROSS JOIN {$this->closure_table} AS subtree
                    WHERE subtree.ancestor = :object_id
                    AND supertree.descendant = :parent_id";

        DB::beginTransaction();
        try {
            DB::connection($this->connection)->statement($delete_sql, [
                'object_id' => $this->id,
            ]);

            DB::connection($this->connection)->statement($insert_sql, [
                'object_id' => $this->id,
                'parent_id' => $parent_id,
            ]);

            DB::commit();
        } catch (\Exception $e) {
            DB::rollback();
            throw new \Exception('Couldn\'t change the parent id. Transaction was rolled back! Details: ' . $e->getMessage());
        }
    }

    /**
     * Checks if a parent id is an existing node.
     *
     * @param int $parent_id
     * @return null | \Exception
     */
    protected function checkParentValidity(int $parent_id)
    {
        if (DB::table("{$this->closure_table}")
                ->where('ancestor', $parent_id)
                ->count() < 1)
        {
            throw new \Exception('No ancestor row existing for this parent id!');
        }
    }

    /**
     * Checks if an entity already has a parent node record.
     *
     * @param int $parent_id
     * @return boolean
     */
    protected function hasExistingParent()
    {
        return DB::table("{$this->closure_table}")
                ->where('descendant', $this->id)
                ->where('ancestor', '!=', $this->id)
                ->count() > 0;
    }
}