Laravel-datatables: Displaying data from relationship in row detail (child row)

[knubbe82]

Summary of problem or feature request

I want to display orders.type and employer.name in child row (row details)

Code snippet of problem

{
"draw": 0,
"recordsTotal": 2,
"recordsFiltered": 2,
"data": [
{
"id": "5",
"company_id": "2",
"number": "001",
"date": "2017-11-19",
"start_time": "10:00:00",
"end_time": "13:00:00",
"orders": [
{
"id": "18",
"company_id": "2",
"employer_id": "3",
"type": "Zamena memorije",
"description": "Potrebno zameniti memoriju",
"status": "1",
"created_at": "2017-11-20 20:56:44",
"updated_at": "2017-11-20 21:00:08",
"pivot": {
"work_id": "5",
"order_id": "18"
},
"employer": {
"id": "3",
"company_id": "2",
"name": "Igor Jaksic",
"phone": "064/14300250",
"email": "[email protected]",
"created_at": "2017-10-29 11:57:03",
"updated_at": "2017-10-29 11:57:03"
}
},
{
"id": "19",
"company_id": "2",
"employer_id": "3",
"type": "Ugradnja Hard Diska",
"description": "Ugraditi SSD disk 256GB",
"status": "1",
"created_at": "2017-11-20 20:57:26",
"updated_at": "2017-11-20 21:00:08",
"pivot": {
"work_id": "5",
"order_id": "19"
},
"employer": {
"id": "3",
"company_id": "2",
"name": "Igor Jaksic",
"phone": "064/14300250",
"email": "[email protected]",
"created_at": "2017-10-29 11:57:03",
"updated_at": "2017-10-29 11:57:03"
}
},
{
"id": "20",
"company_id": "2",
"employer_id": "3",
"type": "Reinstalacija sistema",
"description": "Reinstalirati sistem",
"status": "1",
"created_at": "2017-11-20 20:57:53",
"updated_at": "2017-11-20 21:00:08",
"pivot": {
"work_id": "5",
"order_id": "20"
},
"employer": {
"id": "3",
"company_id": "2",
"name": "Igor Jaksic",
"phone": "064/14300250",
"email": "[email protected]",
"created_at": "2017-10-29 11:57:03",
"updated_at": "2017-10-29 11:57:03"
}
}
],
"action": "<a href=\"http://subnetapp.dev/company/work/5\" class=\"btn btn-info btn-xs\"><i class=\"glyphicon glyphicon-eye-open\"></i></a> <a onclick=\"editForm(5)\" class=\"btn btn-primary btn-xs\"><i class=\"glyphicon glyphicon-edit\"></i></a> <a href=\"http://subnetapp.dev/company/work/restore/5\" class=\"btn btn-warning btn-xs\"><i class=\"glyphicon glyphicon-repeat\"></i></a> <a onclick=\"deleteData(5)\" class=\"btn btn-danger btn-xs\"><i class=\"glyphicon glyphicon-trash\"></i></a>"
},
{
"id": "6",
"company_id": "2",
"number": "002",
"date": "2017-11-19",
"start_time": "11:00:00",
"end_time": "00:00:00",
"orders": [
{
"id": "21",
"company_id": "2",
"employer_id": "7",
"type": "podesavanje stampaca",
"description": "Podesiti stampace",
"status": "1",
"created_at": "2017-11-20 20:58:09",
"updated_at": "2017-11-20 21:02:10",
"pivot": {
"work_id": "6",
"order_id": "21"
},
"employer": {
"id": "7",
"company_id": "2",
"name": "Pera Peric",
"phone": "0641112221",
"email": "[email protected]",
"created_at": "2017-11-09 21:13:17",
"updated_at": "2017-11-09 21:13:17"
}
},
{
"id": "22",
"company_id": "2",
"employer_id": "7",
"type": "Problem sa digitalnim potpisom",
"description": "Problem sa digitalnim potpisivanjem u APR",
"status": "1",
"created_at": "2017-11-20 20:58:37",
"updated_at": "2017-11-20 21:02:10",
"pivot": {
"work_id": "6",
"order_id": "22"
},
"employer": {
"id": "7",
"company_id": "2",
"name": "Pera Peric",
"phone": "0641112221",
"email": "[email protected]",
"created_at": "2017-11-09 21:13:17",
"updated_at": "2017-11-09 21:13:17"
}
}
],
"action": "<a href=\"http://subnetapp.dev/company/work/6\" class=\"btn btn-info btn-xs\"><i class=\"glyphicon glyphicon-eye-open\"></i></a> <a onclick=\"editForm(6)\" class=\"btn btn-primary btn-xs\"><i class=\"glyphicon glyphicon-edit\"></i></a> <a href=\"http://subnetapp.dev/company/work/restore/6\" class=\"btn btn-warning btn-xs\"><i class=\"glyphicon glyphicon-repeat\"></i></a> <a onclick=\"deleteData(6)\" class=\"btn btn-danger btn-xs\"><i class=\"glyphicon glyphicon-trash\"></i></a>"
}
],
"input": []
}

System details

• Operating System: OS X 10.11.6
• PHP Version: 7.1
• Laravel Version: 5.5
• Laravel-Datatables Version: 8

[yajra]

See this demo https://datatables.yajrabox.com/eloquent/row-details for ref. Thanks!

[Philwn]

That link doesnt help us add model relationship data to the table?

`$project = Project::leftJoin('users','projects.leadBy','=','users.id')->join('status', 'projects.status_id', '=', 'status.id')
->join('brand_categories', 'projects.category_id', '=', 'brand_categories.id')
->select(array('projects.id','projects.proj','projects.title','projects.launchdate','projects.leadBy','users.name as leadbyname','projects.textWritten','projects.translations','projects.images','status.name'))
->where('leadBy', \Auth::user()->id)
->where('status_id', '<', 3);

return Datatables::of($project)
->addColumn('operations','<a href="{{ URL::route( \'projects.edit\', [\'id\' => $id ]) }}" class="btn btn-sm btn-info"><i class="fa fa-edit"></i></a>')
// you can also give a function as parameter to editColumn and addColumn instead of blade string
->addColumn('send', '<a href="{{ URL::route( \'project.send\', [\'id\' => $id ]) }}" class="btn btn-sm btn-warning"><i class="fa fa-envelope"></i></a>')
 ->addColumn('category',  ": ")
->make(true);`

As you can see I am adding a column category which is a relation of project. laravel model would be $project->category->name so how can I add this in the column?

[abdelAudace]

• 1

  I got the same issue to display a "quantity" column which is in my pivot table inside my datatable...

  How can i do please?

[abdelAudace]

+1 someone did it please ?

[lilessam]

Ok so just in case if someone still looking for a solution, The main issue that when you return a hasMany relation like the following you won't get a correct stringified JSON text that you can parse using JSON.parse in your JS side.

So to solve this you'll need to add the relationship column to the rawColumns array.

Here's an example.

return DataTables::eloquent(Task::with('subtasks'))
                ->addColumn('subtasks', function (Task $task) {
                    return json_encode($task->subtasks->all());
                })
                ->rawColumns(['subtasks'])
                ->make(true);

function format ( data ) {
    var $markup = '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">';
    var subtasks = JSON.parse(data.subtasks);

    subtasks.forEach(subtask => {
        $markup += '<tr><td>' + subtask.name + '</td></tr>';
    })

    $markup += '</table>';

    return $markup;
}

$(function() {
    var table = $('#tasks-table').DataTable({
       processing: true,
       serverSide: true,
       ajax: '/admin/tasks/list',
       columns: [
            {
                "className": 'details-control',
                "orderable": false,
                "data": 'expandable_icon',
                "name": 'expandable_icon',
                "defaultContent": ''
            },
            { data: 'task_name', name: 'task_name' },
        ]
    });

    $('#tasks-table tbody').on('click', 'td.details-control', function () {
        var tr = $(this).closest('tr');
        var row = table.row( tr );

        if ( row.child.isShown() ) {
            // This row is already open - close it
            row.child.hide();
            tr.removeClass('shown');
        }
        else {
            // Open this row
            row.child( format(row.data()) ).show();
            tr.addClass('shown');
        }
    });
});