How do I retrieve and display data by jquery Datatable in Laravel?

Question

Basically my intention is to fetch data from a database and display it using datatable API in laravel 5.2. However, I keep getting this error/notice DataTables warning: table id=payments-table - Ajax error. For more information about this error,please seehttp://datatables.net/tn/7

Kindly note I have set up my development environment as directed here: https://github.com/yajra/laravel-datatables (jQuery DataTables API for Laravel 4|5 )

My table name is payments.

This is my routes code:

Route::controller('payments', 'admin\paymentsController', [
            'anyData'  => 'payments.data',
            'getIndex' => 'datatables',
        ]);

Then the controller in the admin folder code:

<?php
namespace App\Http\Controllers\admin;
use App\Http\Controllers\Controller;
use DB;
use Illuminate\Http\Request;

use App\Http\Requests;
use Yajra\Datatables\Datatables;

class paymentsController extends Controller
{
    /**
     * Displays datatables front end view
     *
     * @return \Illuminate\View\View
     */
    public function getIndex()
    {
        return view('admin.payments', compact('payments'));
    }

    /**
     * Process datatables ajax request.
     *
     * @return \Illuminate\Http\JsonResponse
     */
    public function anyData()
    {

        return Datatables::of(payments::query())->make(true);

    }
}

Then finally my view in the admin folder:

@extends('layouts.master')

@section('content')
    <table class="table table-bordered" id="payments-table">
        <thead>
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Amount</th>
        </tr>
        </thead>
    </table>
@stop

@push('scripts')
<script>
    $(function() {
        $('#payments-table').DataTable({
            processing: true,
            serverSide: true,
            ajax: '{!! route('payments.data') !!}',
            columns: [
                { data: 'id', name: 'id' },
                { data: 'name', name: 'name' },
                { data: 'amount', name: 'amount' },
                 ]
        });
    });
</script>
@endpush

Please help me where is the mistake. Thanks.

Answer 1

Ooh (sigh) finally, I solved my mess.

Well for the sake of anyone who would want to know how, in my controller class I changed return Datatables::of(payments::query())->make(true);

to return Datatables::of(DB::table('payments'))->make(true);

I am not too sure but I think at first I was not able to get the data from the database.

so my controller class becomes:

<?php
namespace App\Http\Controllers\admin;
use App\Http\Controllers\Controller;
use DB;
use Illuminate\Http\Request;

use App\Http\Requests;
use Yajra\Datatables\Datatables;

class paymentsController extends Controller
{
    /**
     * Displays datatables front end view
     *
     * @return \Illuminate\View\View
     */
    public function getIndex()
    {
        return view('admin.payments', compact('payments'));
    }

    /**
     * Process datatables ajax request.
     *
     * @return \Illuminate\Http\JsonResponse
     */
    public function anyData()
    {


        return Datatables::of(DB::table('payments'))->make(true);
    }
}

Everything remains. Thank you.

Answer 2

Try this example:

JS:

$('#user_role_table').dataTable({
            "sServerMethod": "GET",
            //"bProcessing": true,
            "bServerSide": true,
            "sAjaxSource": "{{url('/getusers')}}",
            "aoColumns": [ { "bSortable": true }, { "bSortable": false }, { "bSortable": true }, { "bSortable": false }],
        });

Controller function:

public function getusers()
    {
        $start  = $_REQUEST['iDisplayStart'];
        $length = $_REQUEST['iDisplayLength'];
        $sSearch = $_REQUEST['sSearch'];

        $col = $_REQUEST['iSortCol_0'];

        $arr = array(0 => 't1.name', 2 => 't3.name');

        $sortBy = $arr[$col];
        $sortType = $_REQUEST['sSortDir_0'];

        $getRoles = "select t1.id, t1.name as user_name, t1.email, t3.id as role_id, t3.name as role FROM users t1 JOIN role_user t2 ON t1.id = t2.user_id JOIN roles t3 on t2.role_id = t3.id where t1.status = '1' ".$userFilter.$condition." ORDER BY ".$sortBy." ".$sortType." LIMIT ".$start.", ".$length;

        $getRoles = DB::select(DB::raw($getRoles));

        // Get all data into an array
        foreach($getRoles as $result)
        {
            $data[] = (array)$result;
        }

        $iTotal = 0;

        $getUserRoleCount   = "select id from roles";
        $userRoleCount      = DB::select(DB::raw($getUserRoleCount));
        $iTotal             = count($userRoleCount);

        $rec = array(
            'iTotalRecords' => $iTotal,
            'iTotalDisplayRecords' => $iTotal,
            'aaData' => array()
        );

        $k=0;
        if (isset($data) && is_array($data)) {

            foreach ($data as $item) {
                $rec['aaData'][$k] = array(
                    0 => ucwords(strtolower($item['user_name'])),
                    1 => $item['email'],
                    2 => ucwords(strtolower($item['role'])),
                    3 => '<button id="'.$item['id'].'" name="'.$item['user_name'].'" email="'.$item['email'].'" role="'.$item['role_id'].'" class="btn btn-xs btn-info edit_user" href="javascript:void(0);"><i class="fa fa-edit"></i>User</button>'.'&nbsp;&nbsp;'.'<button id="'.$item['id'].'" role="'.$item['role_id'].'" class="btn btn-xs btn-info edit_permission" href="javascript:void(0);"><i class="fa fa-edit"></i>Permission</button>'
                );
                $k++;
            }
        }

        echo json_encode($rec);
    }

I am using the same and it is working fine.