Server-side Yajra datatable columns:[] manipulation

Question

I want to manipulate yajra datatables

columns:[
    { data: 'fname', name: 'members.fname' },
    { data: 'fname', name: 'employees.emp_fname' }
]

using the render function shown below and it didn't workout. There is an error in the return statement. The table doesn't render any data. I just couldn't find the right workaround. I already used alias and addColumn and works good but the problem is it won't allow searching in names. That is why I came up with this kind of code line because it will maybe allow searching if it works. Please help on this. Thanks.

p3bills_table = $('#table-p3bills').DataTable({
    processing: true,
    serverSide: true,
    order: [
        [
            1, "asc"
        ]
    ],
    ajax: {
        url: "{{ route('get.p3bills') }}",
    },
    columns: [
        { data:'id', name:'p3bills.id' },
        { data:'id', name:'p3bills.id', orderable: true },
        { data:'p3loan_id', name:'p3loan_id' },
        { data:'schedule', name:'p3bills.schedule' },
        {
            render: function (data, type, full, meta) {
                if (full.member_id == 0) {
                    return data:'fname', name: 'employees.emp_fname';
                } else {
                    return data:'fname', name: 'members.fname';
                }
            }
        },
        { data:'lname', name:'members.lname' },
        { data:'mname', name:'members.mname' }
    ]
});

Controller

 public function getP3bills(){

    $p3bills = P3bill::leftJoin('p3payments', 'p3payments.p3bill_id', '=', 'p3bills.id')
                     ->leftJoin('members', 'members.id', '=', 'p3bills.member_id')
                     ->leftJoin('employees', 'employees.id', '=', 'p3bills.employee_id')
                     ->select(['members.id','members.lname','members.fname','members.mname','members.extension','employees.id','employees.emp_lname','employees.emp_fname','employees.emp_mname','employees.emp_extension','p3bills.*','p3payments.payment_amount','p3payments.or_number'])
                     ->selectRaw(  '('.
            'CASE'.
            ' WHEN members.id=0 THEN employees.emp_fname'.
            ' ELSE members.fname'.
            ' END'.
        ') AS person_name' );
    }

Answer 1

You need to handle this situation on the backend instead.

I assume you're using Yajra DataTables service implementation.

You can use CASE statement to determine what will be returned based on your condition.

For example:

/**
 * Get query source of dataTable.
 *
 * @param \App\Models\User $model
 * @return \Illuminate\Database\Eloquent\Builder
 */
public function query(User $model)
{
    $query = $model->newQuery()
        ->select()
        ->selectRaw(
            '('.
                'CASE'.
                ' WHEN members.id=0 THEN employees.emp_fname'.
                ' ELSE members.fname'.
                ' END'.
            ') AS person_name'
        );

    return $query;
}

To enable searching on this new column you would need to call filterColumn() method.

Depending on your Laravel DataTables version and chosen implementation method your code may be different. For example, code below is for Laravel DataTables v9 using service implementation.

/**
 * Build DataTable class.
 *
 * @param  mixed  $query Results from query() method.
 * @return \Yajra\DataTables\DataTableAbstract
 */
public function dataTable($query)
{
    return datatables($query)
        ->filterColumn('person_name', function($query, $keyword) {
            $query->whereRaw(
                '('.
                    'CASE'.
                    ' WHEN members.id=0 THEN employees.emp_fname'.
                    ' ELSE members.fname'.
                    ' END'.
                ') ILIKE ?', 
                [ '%' . sql_escape_like($keyword) . '%' ]
            );
        });
}

Also I am using the following helper method to escape special characters in the LIKE / ILIKE clause.

if (! function_exists('sql_escape_like')) {
    /**
     * Escape special characters for SQL query with LIKE condition.
     *
     * @param  string $value
     * @return string
     */
    function sql_escape_like($value)
    {
        return str_replace(['\\', '%', '_'], ['\\\\', '\\%', '\\_'], $value);
    }
}

Then use { data:'person_name' } as your column definition in your JavaScript DataTables initialization code.

Answer 2

As suggested by Gyrocode.com, check the status in the Controller and render the element using a pseudo column. In the Controller you could do so,

$bills = Bill::all(); // Your Model Query goes here

return datatables($bills)
        ->addColumn('fname', function ($bill) {
            if ($bill->member_id == 0) {
                return $bill->employee->fname; // Employee First Name
            }

            return $bill->member->fname; // Member First Name
        })
        ->toJson();

and in the JavaScript you could use it as,

{ data: 'fname' }