In this tutorial we’ll learn how to handle recursive relationship within database table in a Laravel 9 app. Recursive relationship occurs when a database table has self-referencing foreign key i.e., a record maintains referential integrity using the id of another record within the same table; database tables of categories, directories or staff hierarchy usually have recursive relationship. And then we’ll see how to build tree formation out of recursive table easily by employing laravel-adjacency-list package. Finally, we’ll learn how to display table’s data in the form of tree at client-side using And Design Vue TreeSelect and Tree components. There’s a lot to cover, so let’s begin.
Laravel 9.x requires a minimum PHP version of 8.0. This tutorial assumes that you are using Linux, macOS or WSL on Windows and Node.js, Composer and PHP 8 along with required modules i.e bcmatch, sqlite, mbstring, xml, zip, gd, mcrypt are properly installed. Also make sure that SQLite is installed as we’ll be using SQLite to keep things simple and quick; you may use MySQL or PostgreSQL as you like, however make sure the corresponding PHP module is installed too.
Using Composer, enter following command in terminal to install Laravel 9;
composer create-project laravel/laravel app 9.*
Go inside newly created app folder by executing;
cd app
Now, add Breeze package by executing following command;
composer require laravel/breeze:* --dev
Then install Breeze with Vue and Inertia.js functionality by executing following command;
php artisan breeze:install vue
Let’s also install laravel-adjacency-list package to handle recursive table’s data smartly;
composer require staudenmeir/laravel-adjacency-list:"^1.0"
Next, setup database and Laravel environment file. Here, I’m configuring SQLite database for simplicity purposes.
Being in parent folder (app), execute following command in order to create new SQLite database file;
touch database/database.sqlite
Then find .env config file in the parent folder, open it and amend DB_CONNECTION line as follows;
DB_CONNECTION = sqlite
Remove or comment out DB_HOST, DB_PORT, DB_DATABASE, DB_USERNAME, DB_PASSWORD lines from .env as we are using local SQLite database file.
All configurations done! Now let’s move onto create Category model along with its migration by entering following command:
php artisan make:model Category -m
Let’s open migration file database/migrations/(date_stamp)_create_categories_table.php and insert code in this migration file:
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
public function up()
{
Schema::create('categories', function (Blueprint $table) {
$table->id();
$table->string('title');
$table->unsignedBigInteger('parent_id')->nullable();
$table->foreign('parent_id')->references('id')->on('categories');
$table->timestamps();
});
}
public function down()
{
Schema::dropIfExists('categories');
}
};
Then run migration;
php artisan migrate
Change app/Models/Category.php model file as follows;
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Staudenmeir\LaravelAdjacencyList\Eloquent\HasRecursiveRelationships;
class Category extends Model
{
use HasFactory;
use HasRecursiveRelationships;
protected $fillable = [
'title', 'parent_id'
];
}
In order to insert data in one go, create new seeder by entering following command;
php artisan make:seeder CategorySeeder
Now, insert following code inside database/seeders/CategorySeeder.php;
<?php
namespace Database\Seeders;
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
class CategorySeeder extends Seeder
{
public function run()
{
DB::table('categories')->insert(['title' => 'Books']);
DB::table('categories')->insert(['title' => 'Fiction', 'parent_id' => '1']);
DB::table('categories')->insert(['title' => 'Horror', 'parent_id' => '2']);
DB::table('categories')->insert(['title' => 'Fantasy', 'parent_id' => '2']);
DB::table('categories')->insert(['title' => 'Comedy', 'parent_id' => '2']);
DB::table('categories')->insert(['title' => 'Non-fiction', 'parent_id' => '1']);
DB::table('categories')->insert(['title' => 'History', 'parent_id' => '3']);
DB::table('categories')->insert(['title' => 'Geography', 'parent_id' => '3']);
DB::table('categories')->insert(['title' => 'Politics', 'parent_id' => '3']);
DB::table('categories')->insert(['title' => 'Human Geography', 'parent_id' => '5']);
DB::table('categories')->insert(['title' => 'Physical Geography', 'parent_id' => '5']);
DB::table('categories')->insert(['title' => 'Regional Geography', 'parent_id' => '5']);
}
}
Then seed database by executing following command:
php artisan db:seed --class=CategorySeeder
Having done with data, let’s move on to view side of things; first, install node module ant-design-vue by executing following command in parent (app) folder:
npm install ant-design-vue --save
Then, create a new folder resources/js/Pages/Categories by entering following command in parent folder:
mkdir resources/js/Pages/Categories
Now, create Index.vue inside this folder by entering following command:
touch resources/js/Pages/Categories/Index.vue
Then, insert following code inside Index.vue:
<template>
<div class="min-h-screen flex flex-col sm:justify-center items-center pt-6 sm:pt-0 bg-gray-100">
<div class="w-full sm:max-w-md mt-6 px-6 py-4 bg-white shadow-md overflow-hidden sm:rounded-lg">
<TreeSelect
v-model:value="selected"
:tree-data="data"
:replace-fields="{ children:'children', label:'title', key:'id', value: 'id' }"
show-search
filterTreeNode="true"
treeNodeFilterProp="title"
tree-default-expand-all
class="w-full"></TreeSelect>
</div>
<div class="w-full sm:max-w-md mt-6 px-6 py-4 bg-white shadow-md overflow-hidden sm:rounded-lg">
Selected: {{ selected }}
Expanded Keys: {{ expandedKeys }}
Selected Keys: {{ selectedKeys }}
Checked Keys: {{ checkedKeys }}
</div>
<div class="w-full sm:max-w-md mt-6 px-6 py-4 bg-white shadow-md overflow-hidden sm:rounded-lg">
<Tree
v-model:expandedKeys="expandedKeys"
v-model:selectedKeys="selectedKeys"
v-model:checkedKeys="checkedKeys"
checkable
:tree-data="data"
:field-names="{ children:'children', title:'title', key:'id' }">
</Tree>
</div>
</div>
</template>
<script setup>
import { ref } from 'vue'
import { TreeSelect, Tree } from 'ant-design-vue'
import 'ant-design-vue/dist/antd.css'
const props = defineProps({
data : Object,
})
const selected = ref('')
const expandedKeys = ref('')
const selectedKeys = ref('')
const checkedKeys = ref('')
</script>
Finally, insert following route inside routes/web.php:
Route::get('/categories', function () {
$data = App\Models\Category::tree()->get()->toTree();
return Inertia::render('Categories/Index', ['data' => $data]);
})->name('categories');
Compile UI assets by entering following command:
npm run build
Run development server:
php artisan serv
Then, enter following URL in the browser:
localhost:8000/categories