Closure Table Trait for Laravel Eloquent Models

ClosureTable.php

<?php // app/Http/Traits/ClosureTable.php

namespace App\Models\Traits;

use Exception;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsToMany;
use Illuminate\Support\Facades\Db;

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

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

    /**
     * Returns all "younger" relatives which are underneath
     */
    public function descendants(): BelongsToMany {
        return $this->downwards()->where('descendant', '!=', $this->id);
    }

    /**
     * Returns all upwards elements of a tree ("thicker branches") with the beginning node itself
     */
    public function upwards(): BelongsToMany {
        $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
     */
    public function downwards(): BelongsToMany {
        $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
     */
    public function makeRoot(): void {
        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
     */
    public function setParent(int $parent_id): void {
        if ($this->hasExistingParent()) {
            $this->moveToParent($parent_id);
            return;
        }

        $this->addToParent($parent_id);
    }

    /**
     * Adds a new entity to the tree underneath a parent node
     */
    protected function addToParent(int $parent_id): void {
        $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
     */
    protected function moveToParent(int $parent_id): void {
        $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
     */
    protected function checkParentValidity(int $parent_id): void {
        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
     */
    protected function hasExistingParent(): bool {
        return Db::table("{$this->closure_table}")
            ->where('descendant', $this->id)
            ->where('ancestor', '!=', $this->id)
            ->count() > 0;
    }
}