CODEWITHSUNDEEP
phpjquerymysqldatatableslaravel-5.4
Bhavin Solanki
Bhavin Solanki

Reputation: 4818

yajra/laravel-datatables search is not working with laravel 5.4

I have used yajra/laravel-datatables to create data grid list. I have used joins to retrieve information as per requirement.

Here is join code

$inquiryInvoice = DB::table('inquiry_invoice')->select('inquiry_invoice.*',DB::raw('CONCAT(inquiry_personal.first_name, " ", inquiry_personal.middle_name, " ", inquiry_personal.last_name) AS full_name'), 'inquiry_personal.id as inquiry_personal_id','branches.branch_name',DB::raw('CONCAT(users.first_name, " ", users.last_name) AS user_full_name'), DB::raw('CONCAT(counselor.first_name, " ", counselor.last_name) AS counselor_full_name'), 'inquiry_counselor.user_id as inquiry_counselor_user_id','inquiry_master.workflow_id',DB::raw(' "invoice" AS type'))
                ->join('inquiry_master', 'inquiry_master.id', '=', 'inquiry_invoice.inquiry_id')
                ->join('inquiry_personal', 'inquiry_master.id', '=', 'inquiry_personal.inquiry_id')
                ->join('branches', 'inquiry_master.branch_id', '=', 'branches.id')
                ->join('users', 'inquiry_master.user_id', '=', 'users.id')
                ->leftJoin('inquiry_counselor', 'inquiry_master.id', '=', 'inquiry_counselor.inquiry_id')
                ->leftJoin('users as counselor', 'inquiry_counselor.user_id', '=', 'counselor.id');

Here is code for assign Datatables

return Datatables::of($inquiryInvoice)
            ->escapeColumns([])
            ->make(true);

Here is jQuery code

$dataTable = $('#data-table').DataTable({
            processing: true,
            serverSide: true,
            Filter: true,
            url:'{!! url("transaction_any_data") !!}',
            columns: [
                { data: 'full_name', name: 'full_name' },
                { data: 'branch_name', name: 'branch_name' },
                { data: 'user_full_name', name: 'user_full_name' },
                { data: 'counselor_full_name', name: 'counselor_full_name' }
            ]
        });

Now when i try to search something it is giving me alert with error Here is an error message, enter image description here

Now i want to know,

how can i quick search using JOIN and CONCAT columns?

Is there specific way to do this?

please suggest me the best way...

Upvotes: 1

Views: 7344

Answers (3)

sre
sre

Reputation: 415

It simply doesn't work with POST. Try using GET as Http Method. (At least for Laravel 5.5 and 5.6)

Upvotes: 1

Gyrocode.com
Gyrocode.com

Reputation: 58900

With Laravel DataTables you need to include table name in the columns.name option when you use two or more tables with ambiguous names.

Since full_name is ambiguous name you need to change it to inquiry_invoice.full_name.

For example:

$dataTable = $('#data-table').DataTable({
   processing: true,
   serverSide: true,
   url:'{!! url("transaction_any_data") !!}',
   columns: [
      { data: 'full_name', name: 'inquiry_invoice.full_name' },
      { data: 'branch_name', name: 'branch_name' },
      { data: 'user_full_name', name: 'user_full_name' },
      { data: 'counselor_full_name', name: 'counselor_full_name' }
   ]
});

Another problem is that you need to use HAVING instead of WHERE to search columns produced with CONCAT. You need to use filterColumn() method to produce custom search clause for last three columns.

For example (untested):

return Datatables::of($inquiryInvoice)
        ->escapeColumns([])
        ->filterColumn('branch_name', function($query, $keyword) {
            $query->havingRaw('LOWER(branch_name) LIKE ?', ["%{$keyword}%"]);
        })
        ->filterColumn('user_full_name', function($query, $keyword) {
            $query->havingRaw('LOWER(user_full_name) LIKE ?', ["%{$keyword}%"]);
        })
        ->filterColumn('counselor_full_name', function($query, $keyword) {
            $query->havingRaw('LOWER(counselor_full_name) LIKE ?', ["%{$keyword}%"]);
        })
        ->make(true);

Upvotes: 2

Mayank
Mayank

Reputation: 48

You should be update your PHP version with PHP 7.0 or greater then it'll work fine.

Upvotes: -2

Related Questions