I'm working with Laravel, Filament Admin panel, and staudenmeir/laravel-adjacency-list.
I'm struggling to bring this nested tree structure into the CategoryResource List table through getEloquentQuery() properly.
I have a resource (Categories) which has a hasMany relationship to itself. The DB looks like this: "categories"
- id
- parent_id
- name
I can pass this through to get partially there:
public static function getEloquentQuery(): Builder
{
return parent::getEloquentQuery()->tree()->depthFirst();
}
This does create a nested set of records that has the child Categories listed directly underneath their parent Category.
However this leads to 2 issues that are not ideal:
- First, when working with other plugins like alperenersoy/filament-export this seems to break functionality. The CTE query passed through getEloquentQuery causes an error when trying to export through the alperenersoy package (reverting to a simple getEloquentQuery has the package working properly).
- Second, ideally these nested records in the CategoryResource List table would be also respect an alphabetical order based on their names at their nested level. For example, if we have a Parent Category named "Blue Plan" with 2 child Categories are named "AAA Plan" and "CCC Plan", I would like the Categories to list like so: "Blue Plan" "AAA Plan" "CCC Plan"
I've tried several variations of modifying the resource's getEloquentQuery(). First I attempted to modify the output from the laravel-adjacency-list package:
public static function getEloquentQuery(): Builder
{
$sql = parent::getEloquentQuery()->getExpressionGrammar()->compileOrderByPath();
$sql = substr($sql, 0, -5);
return parent::getEloquentQuery()->tree()->orderByRaw($sql . '), name ASC');
}
Then I tried some variations on recursive query constraints
public static function getEloquentQuery(): Builder
{
$tree = Category::withRecursiveQueryConstraint(function (Builder $query) {
$query->where('categories.name', '=', 'ADVENT');
$query->orderBy('name');
}, function () {
return parent::getEloquentQuery()->tree()->depthFirst();
});
return $tree;
}