Closure Table Laravel Package

Getting Started

Install and publish

composer require franzose/closure-table
php artisan vendor:publish --provider="Franzose\ClosureTable\ClosureTableServiceProvider" --tag=config

The package gives you a base Entity model, a ClosureTable trait, and a query builder that understands ancestor/descendant relationships. Read the README first, then skim src/Models/Entity.php and src/Traits/ClosureTable.php — every public method on the trait is the actual API surface you'll use daily.

Create the two tables. Every entity lives in a normal table plus a companion closure table. The entity table carries the adjacency-list columns; the closure table carries the materialised paths:

Schema::create('categories', function (Blueprint $table) {
    $table->id();
    $table->string('title');
    $table->foreignId('parent_id')->nullable()->index();
    $table->unsignedInteger('position')->nullable();
    $table->timestamps();
});

Schema::create('category_closure', function (Blueprint $table) {
    $table->unsignedBigInteger('ancestor');
    $table->unsignedBigInteger('descendant');
    $table->unsignedInteger('depth');
    $table->primary(['ancestor', 'descendant']);
    $table->index('descendant');
});

The closure table name is derived by suffixing _closure to the model table, so categories → category_closure. Keep the id column types identical between the two tables (bigint vs int mismatches are the #1 install failure).

Wire up the model. Extend the package entity and implement its contract:

namespace App\Models;

use Franzose\ClosureTable\Contracts\EntityInterface;
use Franzose\ClosureTable\Models\Entity;

class Category extends Entity implements EntityInterface
{
    protected $table = 'categories';
    // protected $closureTable = 'category_closure'; // optional explicit override
    protected $fillable = ['title', 'parent_id', 'position'];
}

First use case — a content/page tree. Create a root, add children through the tree API (never by setting parent_id by hand), and read back:

$root  = Category::create(['title' => 'Docs']);
$guide = $root->createChild(['title' => 'Guide']);
$api   = $root->createChild(['title' => 'API']);

$root->descendants()->get();   // guide + api (+ their children)
$api->ancestors()->get();      // root, guide-less path: Docs
$api->parent;                  // Docs
Category::getTree();           // roots with children eager-loaded recursively

That round trip — create root, createChild, descendants() for a listing page, ancestors() for breadcrumbs, getTree() for a sidebar — covers 80% of real usage.

Implementation Patterns

Breadcrumbs. ancestors() returns everything above the node, ordered by depth. Reverse it for root-first rendering:

$trail = $node->ancestors()->get()->reverse()->push($node);

Subtree listing with filters. descendants() returns the package query builder, so you can chain normal Eloquent constraints before get():

$node->descendants()
     ->where('published', true)
     ->orderBy('position')
     ->paginate(25);

This is the main reason to prefer the closure table over recursive CTEs: subtree queries are a single indexed join, no recursion.

Because these builders join the closure table onto your entity table, qualify column names whenever ambiguity is possible — categories.id, categories.title, category_closure.depth. Since v6.2.2 the package disambiguates its own internal closure-table columns (the #252 fix), but your where/orderBy/select clauses are your responsibility. Qualifying always costs nothing and avoids the "ambiguous column name" SQL error the moment you join another table or alias columns.

Rendering a full tree for an API. getTree() loads the whole forest with children preloaded at every level, so ->toArray() yields nested JSON with no N+1:

return CategoryResource::collection(Category::getTree());

Only use this when the tree is bounded (menus, categories, org charts). For anything unbounded, fetch a root's descendants and assemble client-side.

Recursive eager loading when you don't want the whole forest.

Category::with('children.children.children')->whereNull('parent_id')->get();

There is no "load recursively to arbitrary depth" helper — you either fix the depth or use getTree().

Reordering and moving subtrees. Never touch position directly. Use moveTo() with the package's position constants:

use Franzose\ClosureTable\ClosureTable;

DB::transaction(function () use ($node, $target) {
    $node->moveTo($target, ClosureTable::MOVE_AS_LAST_CHILD);
});

Constants cover first/last child and previous/next sibling. A move rewrites many closure rows, so always wrap it in a transaction and expect it to be the slowest write in your app.

Two delete semantics. deleteSubtree() removes the node and everything under it; deleteNode() removes only the node and promotes its children to the parent. Pick one per call site deliberately and document which your admin UI uses. If the entity uses SoftDeletes, the package's configured delete mode decides whether descendants are soft- or hard-deleted — check config/closure-table.php before shipping a "trash" feature.

Form handling. A category form should accept a parent_id and resolve it to a move, not a raw update:

$category->fill($validated);

if ($category->isDirty('parent_id')) {
    $category->moveTo(Category::findOrFail($validated['parent_id']), ClosureTable::MOVE_AS_LAST_CHILD);
}

$category->save();

If you let mass assignment set parent_id directly, the closure table silently diverges from the adjacency list and every descendant query starts lying.

Bulk import / seeding. Wrap the whole import in one transaction and build top-down with createChild() so each insert writes its closure rows. For a few thousand nodes this is fine; beyond that, batch by level and disable model events temporarily, then verify the closure table after seeding.

Repositories and services. Keep tree mutations behind a service class (CategoryTreeService::move(), ::delete(), ::createChild()). Tree integrity is the thing you most want a single choke point for, and it makes the transaction boundary obvious.

Caching. Trees read far more than they write. Cache the rendered tree or the descendant id list under a key derived from the root id, and bust it from an observer on saved/deleted for that subtree's root. Because a move touches multiple nodes, keying the cache per-root is simpler than per-node.

Multiple independent trees in one table. Roots are rows with depth = 0 in the closure table / parent_id IS NULL on the entity. Scope every query to a known root when you host several trees (e.g. per-tenant menus) rather than relying on global getTree().

Gotchas and Tips

Raw writes corrupt the tree. Any Category::insert(), update(['parent_id' => ...]), or ->delete() that bypasses the trait leaves orphaned or missing closure rows, and the corruption is invisible until someone renders breadcrumbs. Guard it: add a deleting observer that delegates to deleteSubtree()/deleteNode(), and treat the closure table as owned by the package.

Qualify columns in closure joins (fixed internally in 6.2.2). Closure queries join categories and category_closure, both of which have overlapping-ish column names once you add your own joins. v6.2.2 (#252/#284) added column disambiguation to the package's own closure-table queries, so upgrade before you start writing selectRaw workarounds. Your own code still needs prefixes: prefer where('categories.published', true) over where('published', true) on descendants()/ancestors() chains, and expect ambiguous column name: id the first time you join categories to another table inside a subtree query.

No concurrency control. Two simultaneous createChild() calls on the same parent can compute the same position. Wrap inserts in a transaction and consider a unique index on (parent_id, position), or a queue/serialised writer, if reordering matters.

Write cost scales with depth, not row count. Inserting a leaf writes depth + 1 closure rows. A 10k-node tree that's 30 levels deep is fine; a degenerate 500-level chain is not. moveTo() is worse — it rewrites the closure rows for the entire subtree.

depth is zero-based. A node's self-row has depth = 0, direct children 1. Off-by-one here is the usual cause of "my children query returns the node itself" — descendants() should already exclude self, but hand-written closure joins won't.

Composite primary key on the closure table. Eloquent can't find() a closure row. Query it through the builder only, and don't try to make a model for it.

Foreign keys + soft deletes fight each other. ON DELETE CASCADE on ancestor/descendant is convenient for hard deletes but does nothing for soft deletes, which is exactly when you forget to clean up. Match the FK strategy to your configured delete mode.

Config caching. After editing config/closure-table.php (delete mode, closure table naming), run php artisan config:cache — stale cached config produces behaviour that contradicts the file you're staring at.

getTree() is an unbounded query. It has no pagination and no depth limit. On a table with 100k categories it will load the whole thing into memory. Audit call sites before they hit production data volumes.

Missing recursive relations show up as N+1, not errors. $node->children without eager loading is one query per node. Profile tree rendering with DB::listen or Laravel Debugbar early; the fix is with('children') or getTree().

Verify invariants after any suspicious operation. These two queries catch nearly every corruption:

// every entity should have exactly one self-row
DB::table('category_closure')->whereColumn('ancestor', 'descendant')->where('depth', 0)->count();

// no closure rows may point at deleted entities
DB::table('category_closure')->leftJoin('categories', 'categories.id', '=', 'category_closure.descendant')
  ->whereNull('categories.id')->count();

Both should be clean; the second returning rows means a delete bypassed the package. Note these hand-written queries need the same column qualification discipline as above — descendant and id are the classic collision pair.

Upgrade pain is usually migrations, not API. Older versions used integer ids and slightly different config keys. When upgrading, diff the published config and confirm the closure column types against your entity table. Within 6.2.x, upgrades are drop-in patches: 6.2.2 is a single query-building fix (no migrations, no API changes), so it's a safe composer update — but do re-run your subtree queries afterwards, since a query that used to throw or silently mis-filter on ambiguous columns may now behave differently.

Extension points worth knowing. Override getClosureTable() for non-standard naming, subclass Entity to add your own tree helpers, and hook creating/deleting events for auditing. The trait's methods are public, so overriding deleteSubtree() to add a policy check or a cache flush is a legitimate pattern — just call parent:: and keep the transaction.