Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I have two tables Categories:
Schema::create('categories', function(Blueprint $table)
{
$table->increments('id');
$table->integer('parent_id')->unsigned()->nullable();
$table->foreign('parent_id')->references('id')->on('categories')->onDelete('set null');
$table->integer('position', false, true);
$table->integer('depth', false, true);
$table->timestamps();
$table->softDeletes();
});
And Categories Closure:
Schema::create('category_closures', function(Blueprint $table)
{
$table->increments('id');
$table->integer('ancestor', false, true);
$table->foreign('ancestor')->references('id')->on('categories')->onDelete('cascade');
$table->integer('descendant', false, true);
$table->foreign('descendant')->references('id')->on('categories')->onDelete('cascade');
$table->integer('depth', false, true);
});
Entries in Categories table is:
ID parent_id position depth
1 null 0 0
2 null 1 0
3 1 0 1
4 3 0 2
How to make query with correct order(flat, not tree list): ID 1,3,4,2 ?
Schema::create('categories', function(Blueprint $table)
{
$table->integer('parent_id')->unsigned()->nullable();
$table->foreign('parent_id')->references('id')->on('categories')->onDelete('set null');
});
you are referencing id of categories table in the categories table itself?
@SachinAgarwal yes, because parent_id is category's ID
@cipsas Why do you reference id of a table in the table itself? That makes no sense.
Maybe they have sub categories?
@SachinAgarwal Why not? it ensure, that parent_id havn't ID with no existing category.
@bashy yes, there is categories tree(with multilevel sub categories)
your database design is awkward. What exactly you trying to achieve?
LOL, when the row gets deleted, update the same row to NULL? HOW IS THAT POSSIBLE D:
$table->foreign('parent_id')->references('id')->on('categories')->onDelete('set null')
@bashy NULLCEPTION!
@toniperic Hahha nullception made my day!
However, what's wrong with that constraint?
And the db is pretty straitght forward - simple closure table pattern, nothing awkward here.
Anyway @cipsas, what do you need such result for? You probably need to join categories and closure table to get the order, then again join the categories - off the top of my head, need to go out now..
@JarekTkaczyk Closure table method is quite new to me, perhaps I am something missing.
I want to iterate categories in blade template file. Order should depend on position also. If I build tree with this function(last comment) https://laracasts.com/discuss/channels/general-discussion/anyone-use-franzoseclosuretable-any-good-or-other , then I have to iterate something like this:
<ul>
@foreach($categories as $category)
<li class="depth-{{$category->depth}}">{{ $category->name }}</li>
@if($subcats = $category->children)
@foreach($subcats as $subcat)
<li class="depth-{{$subcat->depth}}">{{ $subcat->name }}</li>
@endforeach
@endif
@endforeach
</ul>
If there is 3 level or more, it become complicated. I change buildTree to this and looks like it work:
protected function flatTree( Collection &$elements, $parentId = null ) {
$branch = Collection::make();
foreach ( $elements as $element ) {
if ( $element->parent_id == $parentId ) {
$branch->push($element);
$children = self::flatTree( $elements, $element->id );
if ( $children ) {
foreach($children as $child)
$branch->push($child);
}
$elements->forget( $element['id']);
}
}
return $branch;
}
But perhaps there is better way?
Of course, parent_id wouldn't be the same row as current. I wasn't thinking :P
Please or to participate in this conversation.