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;}
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