How to sort and search by data displayed or enum, not by database table?

[Varin6]

I'm using Laravel 5.6 and yajira datatables plugin.

I want to display user status in one of the columns which is a number from 0-5 in the database but I want to show and display it in the column as words, (New, Updated, Initial, etc.)

Method to make the datatable:

    public function usersDatatable()
        {
            $query = User::with('jobrole')->select([
                'users.id',
                'users.first_name',
                'users.last_name',
                'users.email',
                'users.postcode',
                'users.preferred_role_id',
                'users.status',
            ]);

            return Datatables::of($query)
                ->addColumn('jobrole', function (User $user) {
                    return $user->jobrole ? str_limit($user->jobrole->role, 30, '...') : '';
                })
                ->addColumn('status', function (User $user) {
                    return $user->status_name;
                })
                ->addColumn('action', function (User $user) {
                    return '<a href="' . route('users.show',$user->id).'" class="btn btn-sm btn-primary"><i class="fa fa-eye"></i></a>';
                })
                ->make(true);
        }

As you can see, the status is returned as $user->status_name which is an Accessor method on my User Model:

    public function getStatusNameAttribute()
        {
            return UserStatus::getDescription($this->status);
        }

And the UserStatus Enum class has the logic for the status translation from digits to strings:

    namespace App\Enums;

    use BenSampo\Enum\Enum;

    final class UserStatus extends Enum
    {
        const Initial = 0;
        const New = 1;
        const Updated = 2;
        const Synced = 3;
        const Ignore = 4;

        /**
         * Get the description for an enum value
         *
         * @param  int  $value
         * @return string
         */
        public static function getUserStatus(int $value): string
        {
            switch ($value) {
                case self::Initial:
                    return 'Initial';
                break;
                case self::New:
                    return 'New';
                break;
                case self::Updated:
                    return 'Updated';
                break;
                case self::Synced:
                    return 'Synced';
                break;
                case self::Ignore:
                    return 'Ignore';
                break;
                default:
                    return self::getKey($value);
            }
        }
    }

In the view, I fetch the data via jQuery Ajax and datatables my code in the view is here:

    $('#users-table').DataTable({
                    processing: true,
                    serverSide: true,
                    ajax: '{!! route('users') !!}',
                    columns: [
                        { data: 'id', width: '10', name: 'users.id' },
                        { data: null, render:function (data, type, row) {
                                return data.last_name+', '+data.first_name;
                            }, name: 'users.last_name'
                        },
                        { data: 'email', name: 'users.email' },
                        { data: 'postcode', name: 'users.postcode' },
                        { data: 'jobrole', name: 'jobrole.role' },
                        { data: 'status', name: 'users.status' },
                        { data: 'action', width: '10', name: 'action', orderable: false, searchable: false}
                    ]
                });

Now, because of name:user.status the search and ordering will be based on the user.status column which is just digits. Is there a way to force it to use displayed data for search and ordering? Please point me in the right direction. The only thing that works is switching ServerSide off.

[yajra]

You can try using filterColumn and orderColumn api and manually write its custom query handler.

In cases like this, I usually start writing an equivalent query for it the convert it to laravel code.

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.