Tricks

Nested records in a table

Mar 22, 2023
Leonid
Table builder

So you want to display nested records in a table something like this:

Your resource (model) must contain a field: parent_id

Schema::table('file_document_cats', function (Blueprint $table) {
$table->bigInteger('parent_id')->after('id')->nullable()->default(0);
});

And the first thing we need to do is take care of the order in which the records in the table are displayed. Simple sorting won't help here, so we need to list the post IDs in the right order.

I will show this on the example of my resource FileDocumentCatResource, so let's edit the list page: app/Filament/Resources/CatResource/Pages/ListFileDocumentCats.php

protected function getTableQuery(): Builder
{
return static::getResource()::getEloquentQuery()->orderByRaw(sprintf("FIELD(id, %s)", @implode(',', self::getNestedArray())));
}
 
 
public static function getNestedArray($parent_id=0): array
{
static $records = null;
static $ids = [];
if ($records==null)
$records = self::getResource()::getModel()::get(['id', 'parent_id'])->sortByDesc('id')->groupBy('parent_id')->sortBy('parent_id')->toArray();
 
if (isset($records[$parent_id]) && count($records[$parent_id])) {
foreach ($records[$parent_id] as $_id => $_item) {
$ids[] = $_item['id'];
if (isset($records[$_item['id']]) && is_array($records[$_item['id']]) && count($records[$_item['id']]))
self::getNestedArray($_item['id']);
}
}
return $ids;
}

Next, we need to implement a method for displaying prefixes - to visualize the nesting of records. In the main resource class - I have this: app/Filament/Resources/FileDocumentCatResource.php

private static function getNestedPrefix($parent_id, $prefix=''): string
{
static $parents = null;
if ($parents==null)
$parents = self::$model::all()->pluck('parent_id', 'id');
if ($parent_id==0) return '';
if (isset($parents[$parent_id]) && $parents[$parent_id])
$prefix .= self::getNestedPrefix($parents[$parent_id], $prefix.'  ');
return $prefix;
}

And now let's move on to our column in the table:

 
public static function table(Table $table): Table
{
return $table
->columns([
Tables\Columns\TextColumn::make('name')
->getStateUsing(function(FileDocumentCat $record) {
return self::getNestedPrefix($record->id) . ($record->parent_id ? '-- ' : '') . $record->name;
})
->html(),

Don't forget to add the ability to select the parent element to the resource edit form:

public static function form(Form $form): Form
{
return $form
->schema([
Forms\Components\Select::make('parent_id')
->options(function($record) {
$_options = self::getNestedArray('name');
if (isset($record->id)) unset($_options[$record->id]);
return (array) $_options;
})
->default(0),
]);
}
 
 
public static function getNestedArray($name='name', $parent_id=0, $prefix=''): array
{
static $records = null;
static $items = [];
if ($records==null)
$records = self::$model::get(['id', 'parent_id', $name])->sortByDesc('id')->groupBy('parent_id')->sortBy('parent_id')->toArray();
if (isset($records[$parent_id]) && count($records[$parent_id])) {
foreach ($records[$parent_id] as $_item) {
$items[$_item['id']] = $prefix . $_item[$name];
if (isset($records[$_item['id']]) && is_array($records[$_item['id']]) && count($records[$_item['id']]))
self::getNestedArray($name, $_item['id'], $prefix . ' - ');
}
}
return $items;
}
avatar

Getting this error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '), id asc limit 10 offset 0' at line 1

👍 1