我有一个Json文件，它已被传递到我的视图中。我只想把那个Json文件插入到我的Yajra Datatable中。
这是我的Json Data传递到http://127.0.0.1:8000/resolution_time_of_calls:的一个例子
{"draw":0,"recordsTotal":205,"recordsFiltered":205,"data":[{"Month":"1","Name":"Analytics Team","Total":"422","AVG":"211.0000","MAX":"212"},{"Month":"1","Name":"Assembly Team","Total":"823","AVG":"137.1667","MAX":"502"},{"Month":"1","Name":"Big Data Team","Total":"5131","AVG":"570.1111","MAX":"985"},{"Month":"1","Name":"Business Intelligence Team","Total":"5706","AVG":"713.2500","MAX":"1127"},{"Month":"1","Name":"Data Security Team","Total":"57703","AVG":"663.2529","MAX":"4463"},{"Month":"1","Name":"Data Security-SouthPH","Total":null,"AVG":null,"MAX":null},{"Month":"1","Name":"Deskside Team","Total":"34841","AVG":"305.6228","MAX":"1498"},{"Month":"1","Name":"Development and Testing
这是我在resolution_time_of_calls视图中的代码
<table class="table table-bordered" id="table">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
</tr>
</thead>
</table>
<script>
$(function() {
$('#table').DataTable({
processing: true,
serverSide: true,
ajax: '{{ url('getData') }}',
columns: [
{ data: 'Month', name: 'id' },
{ data: 'Name', name: 'name' },
{ data: 'Total', name: 'email' }
]
});
});
</script>
这是我的路线：
Route::get('resolution_time_of_calls', 'DisplayDataController@getData');
我的DisplayDataController控制器：
class DisplayDataController extends Controller
{
function getData(){
return datatables(DB::select("SELECT DISTINCT MONTH(DATE_ADD(T.Created, INTERVAL 8 HOUR)) AS Month ,Q. Name
,SUM(TIMESTAMPDIFF(HOUR,T.Created,O.Created)) AS Total
,AVG(TIMESTAMPDIFF(HOUR,T.Created,O.Created)) AS AVG
,MAX(TIMESTAMPDIFF(HOUR,T.Created,O.Created)) AS MAX
FROM rtdb.Tickets T
LEFT JOIN rtdb.Queues Q ON Q.id=T.Queue
LEFT JOIN rtdb.ObjectCustomFieldValues O ON O.ObjectId=T.EffectiveId
AND O.CustomField=15 AND O.ObjectType='RT::Ticket' AND O.Disabled=0
WHERE T.Status!='Deleted' AND T.IsMerged IS NULL AND T.Type='ticket'
GROUP BY MONTH(DATE_ADD(T.Created, INTERVAL 8 HOUR)),Q.Name Order by Month asc
"))->toJson();
}
}
我希望在我的数据表中有这样的输出：
Month| Name |Total
1 | team a | 100
2 | team b | 200
谢谢！！！
将控制器功能更改为：
function getData(){
return Datatables::of(
DB::select("SELECT DISTINCT MONTH(DATE_ADD(T.Created, INTERVAL 8 HOUR)) AS Month ,Q. Name
,SUM(TIMESTAMPDIFF(HOUR,T.Created,O.Created)) AS Total
,AVG(TIMESTAMPDIFF(HOUR,T.Created,O.Created)) AS AVG
,MAX(TIMESTAMPDIFF(HOUR,T.Created,O.Created)) AS MAX
FROM rtdb.Tickets T
LEFT JOIN rtdb.Queues Q ON Q.id=T.Queue
LEFT JOIN rtdb.ObjectCustomFieldValues O ON O.ObjectId=T.EffectiveId
AND O.CustomField=15 AND O.ObjectType='RT::Ticket' AND O.Disabled=0
WHERE T.Status!='Deleted' AND T.IsMerged IS NULL AND T.Type='ticket'
GROUP BY MONTH(DATE_ADD(T.Created, INTERVAL 8 HOUR)),Q.Name Order by Month asc")
)->make(true);
}
确保在控制器类的顶部包含以下内容：
use YajraDatatablesDatatables;
dataTable初始化
我还将在文档就绪侦听器中包含dataTable加载(除非您在不同的事件上加载该表)：
这将是你的table.blade.php文件(显然缺少很多html，但这只是一个例子)：
<table class="table table-bordered" id="table">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
</tr>
</thead>
</table>
<script>
$(document).ready(function(){
$('#table').DataTable({
processing: true,
serverSide: true,
ajax: '{{ route('getData') }}',
columns: [
{ data: 'Month', name: 'id' },
{ data: 'Name', name: 'name' },
{ data: 'Total', name: 'email' }
]
});
});
</script>
仅供参考-如果你想让服务器完成所有的排序和搜索工作，你只需要将serverSide:设置为True。它可以设置为False，并且仍然使用ajax来获取初始数据。但是，对于非常大的表，最好设置为true。
此外-列数组-数据是要放入列中的键的名称。请确保将列按正确的顺序放在此处。name是设置serverSide处理时的数据库列名。
路线：
由于我使用了一个路由，也一定要在您的web.php文件中为ajax查询设置一个路由：
Route::get('resolutiondata', 'DisplayDataController@getData')->name('getData');
以及显示视图的路线：
Route::get('resolution_time_of_calls', function () {
return view('table');
});
以上路径假设您的视图(table.blade.php)位于文件夹/resources/views/table.blades.php 中