I have a Laravel application where I am using jQuery DataTables (yajra/datatables laravel plugin) to display some records. I am also using 3 child rows to display more detailed information for them. In the child rows I display some extra information, a graph with yearly data of the content, and the values of the record for the last 6 months. The problem is that I cannot fill the last 2 child rows with the data like I filled the first, because I cannot put all the data inside 1 query.
Here is the controller methods that feeds the DataTable
public function getRowDetails()
{
return view('reports.creates', compact('data'));
}
public function getRowDetailsData()
{
$kpi = $this->getUserActiveKpi();
$data = DB::table('reports')
->orderBy('month','desc')
->groupBy('kpi_id')
->take(5)
->get();
return Datatables::of($kpi, $data)
->make(true);
}
private function getUserActiveKpi(){
$user = Auth::user();
$kpis = DB::table('kpis')
->where('kpi_status',1)
->where('responsible_user', $user->id);
return $kpis;
}
this is the initialization script:
$(document).ready(function () {
var table;
table = $('#monthly_table').DataTable({
processing: true,
serverSide: true,
dom: "fr<'clear'>Ttip",
ajax: '{{ url("reports/row-details-data") }}',
tableTools: {.....},
columns: [.....],
order: [[1, 'asc']]
});
});
here are the functions that return the child row data:
var kpi;
function kpi_info(d) {
// `d` is the original data object for the row
return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
'<tr>' +
'<td>KPI:</td>' +
'<td>' + d.kpi_code + '</td>' +
'</tr>' +
'<tr>' +
'<td>Workload:</td>' +
'<td>' + d.kpi_workload + '</td>' +
'</tr>' +
'<tr>' +
'<td>KPI Description:</td>' +
'<td>'+ d.kpi_description + '</td>' +
'</tr>' +
'</table>'
}
function kpi_values(d) {
// `d` is the original data object for the row
kpi = d.id;
// it returns the id, here I want to return the partial view
// that contains the data using the *kpi* as a parameter
return kpi;
}
function kpi_graph(d) {
kpi = d.id;
// it returns the id, here I want to return the partial view that
// contains the graph using the *kpi* as a parameter
return kpi;
}
and here is the function to show them:
$('#monthly_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(kpi_data(row.data())).show();
tr.addClass('shown');
}
});
Now what I am trying to achieve is that when I click one of the links that shows the childrow, feed the kpi parameter to the following controller method
public function data($id){
$data = DB::table('reports')
->where('kpi_id',$id)
->orderBy('month','desc')
->take(5)
->get();
return view('reports.data', compact('data'));
}
If there is anything else you should know please ask, and all the help is appreciated
EDIT
by saying I cannot put all the data inside 1 query I mean that I have to combine 3 queries to do that. First is:
DB::table('kpis')
->where('kpi_status',1)
->where('responsible_user', $user->id);
The second one is:
DB::table('reports')
->where('kpi_id',$id)
->orderBy('month','desc')
->take(5)
->get();
And the third one is:
DB::table('reports')
->where('reports.is_validated',1)
->where('reports.year',$current_year)
->orderBy('month','asc')
->get();
I can't think of a query that can get all these data at once.
Note
there is another query like the last one with the $prev_year parameter.
EDIT 2
Schema::create('kpis', function(Blueprint $table)
{
$table->increments('id');
$table->string('kpi_code');
$table->string('kpi_description');
});
Schema::create('reports', function(Blueprint $table)
{
$table->increments('id');
$table->integer('kpi_id')->unsigned()->nullable();
//fk kpi -> values
$table->float('value');
$table->integer('month',false,false,'2');
$table->integer('year',false,false,'4');
});
Schema::table('reports', function($table){
$table->foreign('kpi_id')->references('id')->on('kpis');
});
This code will need to be edited, but hopefully you get the idea.
I would look at Eloquent: Relationships > Defining Relationships > One To Many as well as the Datables demo site page, EloquentController.php - eloquent.getHasMany.title.
Kpi Model
<?php namespace App;
use Illuminate\Database\Eloquent\Model;
class Kpi extends Model {
/**
* Get the reports for the kpi.
*/
public function reports()
{
return $this->hasMany('App\Report')
->orderBy('month','desc');
}
/**
* Get the Kpis for a specific user
*/
public function scopeForUser($query, $user_id)
{
$query->where('kpi_status',1)
->where('responsible_user', $user_id);
}
}
Report Model
<?php namespace App;
use Illuminate\Database\Eloquent\Model;
class Report extends Model {
/**
* A report belongs to a kpi
*
* @return mixed
*/
public function kpi()
{
return $this->belongsTo('App\Kpi');
}
/**
* Get the reports for a specific year
*/
public function scopeForYear($query, $year)
{
$query->where('is_validated',1)
->where('year',$year)
->orderBy('month','asc');
}
}
Controller
public function getRowDetails()
{
return view('reports.creates');
}
public function getRowDetailsData()
{
if (Auth::guest())
{
return ['status'=>'error', 'nessage' => 'Unauthorized user.'];
}
$user_id = Auth::user()->id;
$kpis = Kpi::with(['reports' => function($q){
$q->take(5);
})->forUser($user_id)->get();
return Datatables::of($kpis)
->make(true);
}
The ->take(5) might limit your query to a total of only 5 records and not 5 reports per KPI. If that is the case check out this article.
Updated per comments
It doesn't look like that plugin you are using offers nested loops
You might be able to do something like this, but I have never used that plugin, so I am unable to give you any more information.
columns: [
{data: 'kpi_code', name: 'kpi'},
{data: 'kpi_workload', name: 'workload'},
{data: 'kpi_description', name: 'description'},
{data: 'reports[0].name', name: 'report_1'},
{data: 'reports[1].name', name: 'report_2'},
{data: 'reports[2].name', name: 'report_3'},
{data: 'reports[3].name', name: 'report_4'},
{data: 'reports[4].name', name: 'report_5'}
]