jeudi 2 août 2018

Use Datatables in Laravel 5

Hy, I want to add pagination and search field on a table. I am using laravel 5.4 and a raw query due to the complexity because I have to extract data from too many tables also I am beginner in laravel. I have go through the docs and did many changes but unfortunately could not get desired result. I am getting error: (DataTables warning: table id=vendors - Ajax error. For more information about this error, please see http://datatables.net/tn/7). SO far here is my code.

view file :

<!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Vendors</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>

<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/dt-1.10.12/datatables.min.css"/>

<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.12/datatables.min.js"></script>
</head>

<body>

   <table class="table table-bordered" id="vendors">
                    <thead>
                          <th> Card Name </th>
                          <th> Phone </th>
                          <th> Emails </th>
                    </thead>                
    </table>

   <script>
    $(document).ready(function () {
        $('#vendors').DataTable({
            "processing": true,
            "serverSide": true,

            "ajax":{

                     "url": "",
                     "dataType": "json",
                     "type": "POST",
                     "data":{ _token: ""}
                   },
            "columns": [
                { "data": "card_name" },
                { "data": "phone" },
                { "data": "email" }
            ]    

        });
    });
</script>
</body>
</html> 

controller :

public function allvendor(Request $request)
    {

    $vendors =  DB::select( DB::raw("SELECT * FROM `bp` inner join pdbp on bp.id = pdbp.bp_id inner join ( SeLECT bid, GROUP_CONCAT(subc_name SEPARATOR ', ') as cat FROM sub_categories sc inner join `bp_categories` bc on sc.subc_cod = bc.subc_cod group by bid) categories on bp.id = categories.bid inner join ( SeLECT bid, GROUP_CONCAT(material SEPARATOR ', ') as mat FROM product_material pm inner join `bp_materials` bm on pm.material_cod = bm.mat_cod group by bid ) materials on bp.id = materials.bid inner join (SeLECT bid, GROUP_CONCAT(description SEPARATOR ', ') as certt FROM cert c inner join `bp_certificate` bcc on c.id = bcc.cid group by bid) certificate on bp.id = certificate.bid"));

$columns = array( 
                        0 =>'card_name', 
                        1 =>'phone',
                        2=> 'email',
                    );

   $totalData = $vendors::count();

    $totalFiltered = $totalData; 

    $limit = $request->input('length');
    $start = $request->input('start');
    $order = $columns[$request->input('order.0.column')];
    $dir = $request->input('order.0.dir');

    if(empty($request->input('search.value')))
    {            
        $posts = vendors::offset($start)
                     ->limit($limit)
                     ->orderBy($order,$dir)
                     ->get();
    }
    else {
        $search = $request->input('search.value'); 

        $posts =  vendors::where('card_name','LIKE',"%{$search}%")
                        ->orWhere('phone', 'LIKE',"%{$search}%")
                        ->offset($start)
                        ->limit($limit)
                        ->orderBy($order,$dir)
                        ->get();

        $totalFiltered = vendors::where('card_name','LIKE',"%{$search}%")
                         ->orWhere('phone', 'LIKE',"%{$search}%")
                         ->count();
    }

    $data = array();
    if(!empty($vendors))
    {
        foreach ($vendors as $vendor)
        {

            $nestedData['card_name'] = $vendor->card_name;
            $nestedData['phone'] = $vendor->phone;
            $nestedData['email'] = $vendor->email;

             $data[] = $nestedData;

        }
    }

    $json_data = array(
                "draw"            => intval($request->input('draw')),  
                "recordsTotal"    => intval($totalData),  
                "recordsFiltered" => intval($totalFiltered), 
                "data"            => $data   
                );

    echo json_encode($json_data);       
}

What is wrong in my code?? Any help would be highly appreciable.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire