Sorting in Yajrabox datatable by Eloquent accessor field

Question

I have a users table with first_name and last_name fields, and an accessor getFullNameAttribute, that I´m displaying as an interactive table using Yajrabox datatable. I can show the table all right, with the 'fullName' accessor, but when I try to sort by this 'fullName' field, nothing really happens. The ajax call gets executed, but the sorting remains the same and no errors are reported. I can sort by any of the other fields and it works fine. Is there a sorting restriction on added model attributes for this package? Can´t seem to find anything on the docs.

Thanks much!

user_4856758 · Answer

To elaborate on my comment, here an answer.

You cannot use virtual properties (e.g. your desired getFullNameAttribute()) for sorting and filtering. Even if it worked, it would be executed in-memory as these accessors cannot be translated to SQL by Laravel automatically.

Instead, you can re-create the desired accessor within SQL. This alone doesn't suffice though, as yajra's DataTables package is not capable of sorting by expression but only by column name. Therefore, you'll have to wrap the whole query in another one. Example:

class UserDataTable extends DataTable
{
    public function query(): \Illuminate\Database\Query\Builder
    {
        return DB::query()->fromSub(
            User::query()
                ->select([
                    '*',
                    DB::raw("CONCAT(first_name, ' ', last_name) as full_name")
                ]),
            'wrapped'
        );
    }

    protected function getColumns(): array
    {
        return [
            ['data' => 'first_name', 'title' => 'First Name'],
            ['data' => 'last_name', 'title' => 'Last Name'],
            ['data' => 'full_name', 'title' => 'Full Name'],
        ];
    }
}

Please be aware that wrapping the table with DB::query()->fromSub(...) prevents you from using type-hints within the filter section:

public function dataTable($query): DataTableAbstract
{
    /** @var QueryDataTable $dataTable */
    $dataTable = datatables($query);

    return $dataTable
        ->editColumn('first_name', function ($user) {
            /** @var User|\stdClass $user */
            return ucfirst($user->first_name);
        });
}

Using /** @var User|\stdClass $user */ is a way to still gain IDE support without the type-hint in the function arguments.

user_3775083 · Answer

For them who do not want to use DB::raw() function.

You can sort by any one column from both ideally first column i.e. first_name.

Column::make('user.name', '.')->title(__('Column Title'))

Here is the code

public function dataTable($query)
{
    return datatables()
        ->eloquent($query)
        ->editColumn('user.name', function (SomeModel $model) {
            return $model->user->name; // here name is the accessor
        })
        ->editColumn('created_at', function (FileCharge $model) {
            return $model->created_at?->diffForHumans();
        });
}

protected function getColumns()
{
    return [
        Column::make('id')->title('#'),
        Column::make('user.name', 'user.first_name')->title(__('User Name')),
        Column::make('created_at')->title(__('Created At')),
    ];
}

Sorting in Yajrabox datatable by Eloquent accessor field

2 Answers