Using Closure Table ( for Hierarchical Data -- breadcrumbs, threaded comments, etc ) in Laravel 4 / MySQL

LaravelHierarchy

<?php


namespace Geleyi\Helpers;

use Illuminate\Support\Facades\DB;

/**
 * Class Hierarchy
 *
 * SQL Schema:
 * ***********************
 *   create table closures('MYS
 *      parent int(11) not null
 *      , child int(11) not null
 *      , depth int(11) not null
 *      , model varchar(16) not null
 *    );
 * ************************
 * @package Geleyi\Helpers
 */
class Hierarchy
{

    public $table;
    public $closureTable = 'closures';

    public function __construct($tableName, $closureTable = NULL)
    {
        $this->table = $tableName;

        if ($closureTable != NULL) $this->closureTable = $closureTable;

    }

    /**
     * Helper to nest array
     * @link    http://stackoverflow.com/questions/841014/nested-sets-php-array-and-transformation/886931#886931
     */

    public static function nestify(array $nodes, $key = 'parent')
    {
        //@todo: implement
    }

    /**
     * Add a node (as last child)
     *
     * @param array $data
     * @param null $targetId
     * @param null $model
     * @return bool || integer
     */
    public function addNode(array $data, $targetId = NULL, $model = NULL)
    {
        $targetId = DB::table($this->table)
            ->where('id', '=', $targetId)
            ->pluck('id');

        $lastUpdatedId = DB::table($this->table)->insertGetId($data);

        if ($lastUpdatedId AND $lastUpdatedId > 0) {
            $query = <<<EOT
            INSERT INTO closures(parent, child, depth, model)
            SELECT parent, $lastUpdatedId, depth + 1, '$model'
                FROM closures
                WHERE child =  $targetId
            UNION ALL
            SELECT $lastUpdatedId, $lastUpdatedId, 0, '$model'
EOT;
            // return the recently added ID
            if (DB::statement($query)) return DB::selectOne('SELECT MAX(LAST_INSERT_ID()) AS lastInsertId FROM ' . $this->table);
        }

        return FALSE;
    }

    /**
     * Check if a Node has children
     *
     *
     */
    public function  hasChildren($nodeId)
    {
        $query = "SELECT COUNT(*) childrenCount
                  FROM ( SELECT child FROM closures WHERE parent = ? ) children
                  WHERE child <> ?";
        $results = DB::select($query, [$nodeId, $nodeId]);

        return (bool)$results[0]->childrenCount;
    }

    /**
     * Get the Parent(s) of a particular node
     * returning 0 means this is a base root
     *
     * @param $nodeId
     * @param null $depth
     * @return bool | array
     */
    public function getParent($nodeId, $depth = NULL)
    {

        $query = DB::table($this->table)
            ->join($this->closureTable, $this->table . '.id', '=', $this->closureTable . '.parent')
            ->where($this->closureTable . '.child', '=', $nodeId)
            ->where($this->closureTable . '.parent', '<>', $nodeId);

        if ($depth) {
            $query->where('depth', '=', $depth);
        }

        // generally return 0 means we're at the root
        return ($query->orderBy($this->closureTable . '.parent', 'desc')->get())
            ? $query->orderBy($this->closureTable . '.parent', 'desc')->get() : FALSE;
    }

    /**
     *
     * Get the child(ren) of the node
     * by default it returns without the self
     *
     * @param $nodeId
     * @param bool $self
     * @param null $depth
     * @return bool | array
     */
    public function getChildren($nodeId, $self = FALSE, $depth = NULL)
    {
        $query = DB::table($this->closureTable)
            ->join($this->table, $this->table . '.id', '=', $this->closureTable . '.child');

        if ( $self ) {
            $query->where($this->closureTable . '.parent', '=', $nodeId);

        } else {
            $query->where($this->closureTable . '.parent', '=', $nodeId);
            $query->where($this->closureTable . '.child', '<>', $nodeId);
        }

        if ($depth) {
            $query->where($this->closureTable . '.depth', '=', $depth);
        }

        // generally return 0 means we're at the root
        return ($query->orderBy($this->closureTable . '.parent', 'desc')->get())
            ? $query->orderBy($this->closureTable . '.parent', 'desc')->get() : FALSE;
    }

    /**
     * Delete a Node
     */
    public function deleteNode($nodeId)
    {

    }

    /**
     * Move node with it's children to another node
     */
    public function move($nodeId, $targetId)
    {

    }

}