Laravel: Sort and search by data displayed or enum, not by database table

Question

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: 'user.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.

Answer 1

SOLUTION 1

It would be the best if you had a lookup table for user statuses (similar to jobrole) which you could have joined to get status names.

SOLUTION 2

In your case I would have the code generate raw CASE ... WHEN clause.

For example:

$query = User::with('jobrole')->select([
    'users.id',
    'users.first_name',
    'users.last_name',
    'users.email',
    'users.postcode',
    'users.preferred_role_id',
    \DB::raw("
       (
          CASE
             WHEN status=0 THEN 'Initial'
             WHEN status=1 THEN 'New'
             WHEN status=2 THEN 'Updated'
             WHEN status=3 THEN 'Synced'
             WHEN status=4 THEN 'Ignore'
             ELSE '' 
          END
       ) AS status_name
    ")
]);

Then remove addColumn('status') statement.

Then use { data: 'status_name', name: 'status_name' } in JavaScript.

Please note that using CASE ... WHEN will probably affect performance.

SOLUTION 3

Alternatively you could use filterColumn() method to filter numeric column based on given keyword.

You can only do ordering by numerical status with this solution.

SOLUTION 4

Alternatively you can use client-side processing by removing serverSide: true option and let the jQuery DataTables do the sorting/filtering. You don't need yajra datatables for that, simply return array of objects as JSON string.

That may be simpler but it will work for small datasets under a few thousand records.

Answer 2

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('status', function ($user) {
            if ($user->status == 1) {
                return "New";
            } elseif ($user->status == 2) {
                return "Updated";
            } else {
                return "Pending";
            }

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