Displaying json data on datatables with laravel resources

Question

I have some data i have stored in my table and i have cast to array and accessing it in my resource like this

<?php

namespace App\Http\Resources;

use Illuminate\Http\Resources\Json\JsonResource;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Auth;

class JobRequests extends JsonResource
{
    public $preserveKeys = true;

    /**
     * Transform the resource into an array.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return array|\Illuminate\Contracts\Support\Arrayable|\JsonSerializable
     */
    public function toArray($request)
    {
        $data = DB::select('select order_data from orders where id=9');
        return [
            'email' => json_decode($data[0]->order_data)->personal_data->email,
            'telephone_number' => json_decode($data[0]->order_data)->personal_data->telephone_number,
            'car_registration' => json_decode($data[0]->order_data)->personal_data->car_registration,
            'postal_code' => json_decode($data[0]->order_data)->personal_data->postal_address
            /**
            'commission' => function(){
                            $final_price = 700;
                            return (int)$final_price;
                            }
            */
              ];
    }
}

My data looks like this

{
    "personal_data": {
        "email": "[email protected]",
        "telephone_number": "0999",
        "postal_address": "LON",
        "car_registration": "GB BHG"
    },
    "inperson_diagnostic": {
        "diagnostic_inspection": "67.30",
        "car_wont_start_inspection": "67.30",
        "plugin_diagnostic_inspection": "67.30"
    },
    "tyres": {
        "front_wheels": 1,
        "rear_wheels": 1,
        "wheel_width": 45,
        "wheel_profile": 1,
        "wheel_rim": 1,
        "speed_rating": "w",
        "final_price": 90
    },
    "servicing_and_mot": {
        "mot_with_collection_delivery": 75,
        "major_service": 304.52,
        "full_service": 203.45,
        "interim_service": "149.70",
        "vehicle_health_check": 50
    },
    "inspection_services": {
        "premium_prepurchase_inspection": 146.38,
        "standard_prepurchase_inspection": 104,
        "basic_prepurchase_inspection": 86.44
    },
    "repairs": {
        "ABS wheel speed sensor replacement": 964,
        "ABS pump replacement": 712,
        "Brake pedal switch replacement": 568,
        "Air conditioning regas (R1234yf Gas ONLY)": 469
    }
}

This is the function i am trying to fetch data with

    //Fetch Job Requests
public function jrData(Request $request)
{
    //$data = DB::select('select order_data from orders where id=9');   
    $jobRequest = new JobRequests($request);
    
    $object_json = $jobRequest->toJson();
    $object_array = (array)$object_json;

    
        return Datatables::of($object_array)
                ->addIndexColumn()
                ->addColumn('action', function($row){
   
                       $btn = '<a href="view_job_request/'.$row->id.'" class="edit btn btn-info btn-sm m-2">View</a>';
     
                        return $btn;
                })
                ->rawColumns(['action'])
                ->make(true);

                
                
}

and this is my blade page

<script>
$(function() {
    $('#users-table').DataTable({
        processing: true,
        serverSide: true,
        ajax: '{!! url('jrData') !!}',
        columns: [
            { data: 'id', name: 'id' },
            { data: 'email', name: 'email' },
            { data: 'telephone_number', name: 'telephone_number' },
            { data: 'car_registration', name: 'car_registration' },
            { data: 'postal_code', name: 'postal_code' },
            {data: 'action', name: 'action', orderable: false, searchable: false}
        ]
    });
});
</script>

This is the data returned by my jrData

{"draw":0,"recordsTotal":1,"recordsFiltered":1,"data":[{"0":"{\"email\":\"[email protected]\",\"telephone_number\":\"0900\",\"car_registration\":\"KGB BHG\",\"postal_code\":\"00200\"}","action":"<a href=\"view_job_request\/\" class=\"edit btn btn-info btn-sm m-2\">View<\/a>","DT_RowIndex":1}],"input":[]}

I get this error on my blade file

DataTables warning: table id=users-table - Requested unknown parameter 'id' for row 0. For more information about this error, please see http://datatables.net/tn/4

How can i display the data in the datatables?

Answer 1

Ok here is an example:

You have

$data = DB::select('select order_data from orders where id=9');

This really isn't going to give you the data you need. Try doing this:

$data = YourModel::where('id',$id)->first();

$id is a dynamic id so you can run this to grab anything instead of being static. I'm guessing you are doing a post to get the data, so if you send that through it would be $request->id so you just set it to $id = $request->id; now you're fully dynamic in your eloquent.