i have laravel application using jquery datatables (yajra/datatables laravel plugin) display records. using 3 child rows display more detailed information them. in child rows display information, graph yearly data of content, , values of record last 6 months. problem cannot fill last 2 child rows data filled first, because cannot put data inside 1 query.
here controller methods feeds 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 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 functions return child row data:
var kpi; function kpi_info(d) { // `d` original data object 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` original data object row kpi = d.id; // returns id, here want return partial view // contains data using *kpi* parameter return kpi; } function kpi_graph(d) { kpi = d.id; // returns id, here want return partial view // contains graph using *kpi* parameter return kpi; } and here function 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()) { // row open - close row.child.hide(); tr.removeclass('shown'); } else { // open row row.child(kpi_data(row.data())).show(); tr.addclass('shown'); } }); now trying achieve when click 1 of links shows childrow, feed kpi parameter 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 else should know please ask, , appreciated
edit
by saying i cannot put data inside 1 query mean have combine 3 queries that. first is:
db::table('kpis') ->where('kpi_status',1) ->where('responsible_user', $user->id); the second 1 is:
db::table('reports') ->where('kpi_id',$id) ->orderby('month','desc') ->take(5) ->get(); and third 1 is:
db::table('reports') ->where('reports.is_validated',1) ->where('reports.year',$current_year) ->orderby('month','asc') ->get(); i can't think of query can these data @ once.
note
there query last 1 $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 need edited, idea.
i @ eloquent: relationships > defining relationships > 1 many datables demo site page, eloquentcontroller.php - eloquent.gethasmany.title.
kpi model
<?php namespace app; use illuminate\database\eloquent\model; class kpi extends model { /** * reports kpi. */ public function reports() { return $this->hasmany('app\report') ->orderby('month','desc'); } /** * kpis 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 { /** * report belongs kpi * * @return mixed */ public function kpi() { return $this->belongsto('app\kpi'); } /** * reports 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 query total of 5 records , not 5 reports per kpi. if case check out this article.
updated per comments
it doesn't plugin using offers nested loops
you might able this, have never used plugin, unable give 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'} ] 
Comments
Post a Comment