1. Introduction: Why Use LTREE?

Many applications require hierarchical (tree-like) data:

• LMS: Grade → Subject → Chapter → Lesson
• E-commerce: Category → Subcategory → Product
• Blogs: Topic → Subtopic → Article

Traditionally, developers use parent_id.

But queries like:

• Get all children
• Get all ancestors
• Get full path
• Reorder tree

…become slow and complicated with large datasets.

PostgreSQL LTREE solves this perfectly by storing hierarchical paths directly inside the database.

2. What Is LTREE in PostgreSQL?

ltree is a PostgreSQL data type designed to store label trees, such as:

• root
• root.web
• root.web.php
• root.web.php.laravel

Each level is separated by a dot (.).

LTREE supports powerful operators:

OperatorMeaningpath @> subpathpath is an ancestor of subpathpath <@ superpathpath is a descendant of superpathpath ~ '*.php'wildcard pattern matching

This makes hierarchical queries extremely fast.

3. Enable LTREE in PostgreSQL

Run:

CREATE EXTENSION IF NOT EXISTS ltree;

You must run this once per database.

4. Creating a Categories Table Using LTREE

Example: hierarchical LMS categories such as:

• lms
• lms.programming
• lms.programming.php
• lms.programming.php.laravel

4.1 Laravel Migration

id();
            $table->string('name');
            $table->string('slug')->unique();
            $table->unsignedBigInteger('parent_id')->nullable();
            $table->timestamps();
        });

        DB::statement('ALTER TABLE categories ADD COLUMN path ltree');
        DB::statement('CREATE INDEX categories_path_gist ON categories USING GIST (path);');
    }

    public function down()
    {
        Schema::dropIfExists('categories');
    }
};

Notes:

• Creating a GIST index speeds up hierarchical queries.
• parent_id is optional but helpful for simple tree operations.

5. Category Model in Laravel

belongsTo(self::class, 'parent_id');
    }

    public function children()
    {
        return $this->hasMany(self::class, 'parent_id');
    }
}

path is treated as a string in Laravel, which is fine.

6. Building the LTREE Path Logic (Service Layer)

The path is built as:

Root category:

slug

Child category:

parent.path . '.' . slug

CategoryService.php

name = $data['name'];
        $category->slug = $slug;
        $category->parent_id = $data['parent_id'] ?? null;
        $category->save();

        // Build LTREE path
        if ($category->parent_id) {
            $parent = Category::findOrFail($category->parent_id);
            $category->path = $parent->path . '.' . $slug;
        } else {
            $category->path = $slug;
        }

        $category->save();
        return $category;
    }

    public function update(Category $category, array $data): Category
    {
        if (isset($data['name'])) $category->name = $data['name'];
        if (isset($data['slug'])) $category->slug = $data['slug'];
        if (array_key_exists('parent_id', $data)) $category->parent_id = $data['parent_id'];

        $category->save();

        $this->rebuildPath($category);
        return $category;
    }

    public function rebuildPath(Category $category)
    {
        if ($category->parent_id) {
            $parent = Category::findOrFail($category->parent_id);
            $category->path = $parent->path . '.' . $category->slug;
        } else {
            $category->path = $category->slug;
        }

        $category->save();

        foreach ($category->children as $child) {
            $this->rebuildPath($child);
        }
    }
}

7. Category Controller (REST API)

get();
    }

    public function store(Request $request)
    {
        $data = $request->validate([
            'name' => 'required|string',
            'slug' => 'nullable|string',
            'parent_id' => 'nullable|exists:categories,id',
        ]);

        $category = $this->service->create($data);

        return response()->json([
            'message' => 'Category created',
            'item' => $category
        ], 201);
    }
}

8. LTREE Queries in Laravel

8.1 Get all descendants (subtree)

$descendants = Category::whereRaw('path <@ ?::ltree', [$category->path])
    ->orderBy('path')
    ->get();

8.2 Get all ancestors (breadcrumb)

$ancestors = Category::whereRaw('?::ltree @> path', [$category->path])
    ->orderBy('path')
    ->get();

8.3 Get all categories under a branch

Category::whereRaw('path <@ ?::ltree', ['lms.programming'])->get();

9. Real LMS Example

Assume your LMS has:

• lms
• programming
• php
• laravel
• javascript
• vue
• math
• algebra

LTREE paths:

• lms
• lms.programming
• lms.programming.php
• lms.programming.php.laravel
• lms.programming.javascript
• lms.programming.javascript.vue
• lms.math
• lms.math.algebra

With LTREE you can:

• Fetch all programming subjects:
• path <@ 'lms.programming'
• Get breadcrumb for Laravel:
• ancestors of 'lms.programming.php.laravel'
• Display nested tree simply by ordering by path.

10. Advanced Improvements

Custom Eloquent Scopes

public function scopeDescendantsOf($query, Category $category)
{
    return $query->whereRaw('path <@ ?::ltree', [$category->path]);
}

public function scopeAncestorsOf($query, Category $category)
{
    return $query->whereRaw('?::ltree @> path', [$category->path]);
}

Usage:

Category::descendantsOf($cat)->get();
Category::ancestorsOf($cat)->get();

Final Summary

Using PostgreSQL LTREE with Laravel gives you:

• Clean and elegant hierarchical data modeling
• Extremely fast tree queries
• Full control of ancestors, descendants, branches, and depth
• Perfect fit for LMS, e-commerce, and CMS systems