'How to Display Data in Yajra Datatables Laravel 7?
I want to display data in Laravel using Yajra Datatables, but the problem is that the data can't sync yet.
If I do Dump and Die dd($orders), the number of data displayed is 4, but if I show it in the Yajra Datatables, the data displayed will be 5. In which case, the correct data is the number 4.
How to fix this problem? The following is the code I have created.
Controller
public function index(Request $request)
{
if (request()->ajax()) {
//Query Builder
$startDate = Carbon::parse(request()->startDate)->format('Y-m-d H:i:s');
$endDate = Carbon::parse(request()->endDate)->format('Y-m-d H:i:s');
$orders = DB::connection('database_marketplace')->table('category')
->join('product', 'category.id', '=', 'product.category_id')
->join('order_summary', 'product.id', '=', 'order_summary.product_id')
->join('order', 'order_summary.order_id', '=', 'order.id')
->where('category.id', 6)
->where('order.order_status', 2)
->when(request()->startDate || request()->endDate, function($q) use ($startDate, $endDate) {
$q->whereBetween('order.created_at', [$startDate, $endDate]);
})
->select('product.id', 'product.name', 'product.price', 'product.description', 'product.created_at', 'product.updated_at', DB::raw('sum(order_summary.quantity) as quantity'))
->groupBy('product.id')
->get();
//End Query Builder
// dd($orders);
return DataTables::of($orders)
->addIndexColumn()
->editColumn('name', function($item) {
return $item->name;
})
->editColumn('order_count', function ($item) {
return $item->quantity;
})
->editColumn('ingredient', function($item) {
$data = \Str::between($item->description, 'Bahan-bahan:','Cara Membuat');
return nl2br($data);
})
->rawColumns(['name','order_count', 'ingredient'])
->make(true);
}
return view('admin.marketplace.shoppingList.index',[
'title' => 'Belanja',
'subtitle' => 'Daftar Belanja',
'date' => $request->date ?? null,
'startDate' => $request->startDate ?? null,
'endDate' => $request->endDate ?? null,
]);
}
Blade View
@extends('layouts.app')
@section('title', "{$title}")
@section('styles')
<link rel="preconnect" href="https://fonts.googleapis.com">
<link rel="preconnect" href="https://fonts.gstatic.com" crossorigin>
<link href="https://fonts.googleapis.com/css2?family=Nunito+Sans:ital,wght@0,400;0,600;0,700;1,400;1,600;1,700&display=swap" rel="stylesheet">
<script type="text/javascript" src="https://cdn.jsdelivr.net/momentjs/latest/moment.min.js"></script>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.min.js"></script>
<link rel="stylesheet" type="text/css" href="https://cdn.jsdelivr.net/npm/daterangepicker/daterangepicker.css" />
<style>
table thead th {
font-family: 'Nunito Sans';
font-style: normal;
font-weight: 600;
font-size: 13px;
line-height: 18px;
/* stroke */
color: #676767;
}
table tbody tr td {
font-family: 'Nunito Sans', sans-serif;
font-style: normal;
font-weight: 600;
font-size: 13px;
line-height: 18px;
color: #676767;
}
.toolbar {
float: right;
}
.date-filter {
float: left;
}
</style>
@endsection
@section('content')
<div class="card">
<div class="card-header d-flex justify-content-between">
<h4 class="text-dark">
{{ $subtitle }}
</h4>
{{--
<div class="d-flex">
--}}
<form class="form-inline" action="{{ route('marketplace.shoppingList.index') }}" method="GET">
<div class="form-group mx-sm-3 mb-2">
<label for="dateFilter" class="sr-only">Filter</label>
<input type="text" id="dateFilter" max="{{ date('Y-m-d') }}" class="form-control rounded mr-2" value="{{ old('date') ? old('date') : $date }}">
<input type="hidden" name="startDate" id="startDate" value="{{ old('startDate') ? old('startDate') : $startDate }}">
<input type="hidden" name="endDate" id="endDate" value="{{ old('endDate') ? old('endDate') : $endDate }}">
</div>
<button type="submit" class="btn btn-primary w-100 rounded">
Submit
</button>
</form>
{{-- <a href="#" id="export" class="btn btn-primary w-100 rounded">Download PDF</a> --}}
{{--
</div>
--}}
</div>
<div class="card-body">
<div class="table-responsive">
<table id="table-order" class="table table-striped table-bordered" style="width: 100%">
</table>
</div>
</div>
<div class="card-footer bg-whitesmoke">
Panen-panen Admin
</div>
</div>
@endsection
@section('modal')
<script type="text/javascript">
$.ajaxSetup({
headers: {
'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
}
});
$(function() {
oTable = $('#table-order').DataTable({
"columnDefs": [{
"defaultContent": "-",
"targets": "_all"
}],
processing: true,
serverSide: true,
ajax: {
url: '{{url()->current()}}'
},
columns: [
{
data: 'DT_RowIndex',
name: 'DT_RowIndex',
orderable: false,
searchable: false,
title: 'No',
width: '5%',
},
{
data: 'name',
name: 'name',
title: 'Nama Produk',
width: '45%'
},
{
data: 'order_count',
name: 'order_count',
title: 'Jumah Pesanan',
width: '10%'
},
{
data: 'ingredient',
name: 'ingredient',
title: 'Bahan-bahan',
width: '40%'
},
],
});
});
</script>
{{-- Date time Range Picker --}}
<script>
$(function() {
$('#dateFilter').daterangepicker({
opens: 'left',
autoUpdateInput: false,
minDate: '2021-01-01 00:00:00',
timePicker: true,
timePicker24Hour: true,
locale: {
cancelLabel: 'Clear',
format: 'YYYY-MM-DD HH:mm:ss',
},
});
$('#dateFilter').on('apply.daterangepicker', function(ev, picker) {
$(this).val(picker.startDate.format('YYYY-MM-DD HH:mm:ss') + ' - ' + picker.endDate.format('YYYY-MM-DD HH:mm:ss'));
$('#startDate').val(picker.startDate.format('YYYY-MM-DD HH:mm:ss'));
$('#endDate').val(picker.endDate.format('YYYY-MM-DD HH:mm:ss'));
});
$('#dateFilter').on('cancel.daterangepicker', function(ev, picker) {
$(this).val('');
});
});
</script>
@endsection
Solution 1:[1]
SOLVED
public function index(Request $request)
{
if (request()->ajax()) {
//Query Builder
$startDate = Carbon::parse(request()->startDate)->format('Y-m-d H:i:s');
$endDate = Carbon::parse(request()->endDate)->format('Y-m-d H:i:s');
$orders = DB::connection('database_marketplace')->table('category')
->join('product', 'category.id', '=', 'product.category_id')
->join('order_summary', 'product.id', '=', 'order_summary.product_id')
->join('order', 'order_summary.order_id', '=', 'order.id')
->where('category.id', 6)
->where('order.order_status', 2)
->when(request()->startDate || request()->endDate, function($q) use ($startDate, $endDate) {
$q->whereBetween('order.created_at', [$startDate, $endDate]);
})
->select('product.id', 'product.name', 'product.price', 'product.description', 'product.created_at', 'product.updated_at', DB::raw('sum(order_summary.quantity) as quantity'))
->groupBy('product.id')
->orderBy('name','asc')
->get();
//End Query Builder
// dd($orders);
return DataTables::of($orders)
->addIndexColumn()
->editColumn('name', function($item) {
return $item->name;
})
->editColumn('quantity', function ($item) {
return $item->quantity;
})
->editColumn('ingredient', function($item) {
$data = \Str::between($item->description, 'Bahan-bahan:','Cara Membuat');
return nl2br($data);
})
->rawColumns(['name','quantity', 'ingredient'])
->make(true);
}
return view('admin.marketplace.shoppingList.index',[
'title' => 'Belanja',
'subtitle' => 'Daftar Belanja',
'date' => $request->date ?? null,
'startDate' => $request->startDate ?? null,
'endDate' => $request->endDate ?? null
]);
}
blade.php
<script type="text/javascript">
$.ajaxSetup({
headers: {
'X-CSRF-TOKEN': $('meta[name="csrf-token"]').attr('content')
}
});
$(function() {
oTable = $('#table-order').DataTable({
"columnDefs": [{
"defaultContent": "-",
"targets": "_all"
}],
processing: true,
serverSide: true,
ajax: {
url: '{{url()->current()}}'
},
columns: [
{
data: 'DT_RowIndex',
name: 'DT_RowIndex',
orderable: false,
searchable: false,
title: 'No',
width: '5%',
},
{
data: 'name',
name: 'name',
title: 'Nama Produk',
width: '45%'
},
{
data: 'quantity',
name: 'quantity',
title: 'Jumah Pesanan',
width: '15%'
},
{
data: 'ingredient',
name: 'ingredient',
title: 'Bahan-bahan',
width: '35%'
},
],
});
$('#dateFilter').daterangepicker({
opens: 'left',
autoUpdateInput: false,
minDate: '2021-01-01 00:00:00',
timePicker: true,
timePicker24Hour: true,
locale: {
cancelLabel: 'Clear',
format: 'YYYY-MM-DD HH:mm:ss',
},
});
$('#dateFilter').on('apply.daterangepicker', function(ev, picker) {
$(this).val(picker.startDate.format('YYYY-MM-DD HH:mm:ss') + ' - ' + picker.endDate.format('YYYY-MM-DD HH:mm:ss'));
$('#startDate').val(picker.startDate.format('YYYY-MM-DD HH:mm:ss'));
$('#endDate').val(picker.endDate.format('YYYY-MM-DD HH:mm:ss'));
//sent as ajax request (startDate and endDate) to server
oTable.ajax.url('{{url()->current()}}?startDate=' + $('#startDate').val() + '&endDate=' + $('#endDate').val()).load();
//reload datatable
oTable.ajax.reload();
});
//Reset date filter
$('#dateFilter').on('cancel.daterangepicker', function(ev, picker) {
$(this).val('');
$('#startDate').val('');
$('#endDate').val('');
//sent as ajax request (startDate and endDate) to server
oTable.ajax.url('{{url()->current()}}').load();
//reload datatable
oTable.ajax.reload();
});
//Export current data to PDF
$('#export').click(function(e) {
e.preventDefault();
var startDate = $('#startDate').val();
var endDate = $('#endDate').val();
if (startDate == '' || endDate == '') {
alert('Silahkan pilih tanggal terlebih dahulu');
} else {
// Sent to route name shoppingList.export
window.location.href = '{{ route('marketplace.shoppingList.export') }}?startDate=' + startDate + '&endDate=' + endDate, '_blank';
}
});
});
</script>
Solution 2:[2]
You can do it this way:
Blade view
<table class="table align-middle table-hover table-nowrap mb-0"
id="table_id"
role="grid"
aria-describedby="example2_info"
style="width:100%"
>
<thead class="table-light">
<tr>
<th>#</th>
<th>Name</th>
<th>Email</th>
<th>Job Position</th>
<th>Phone</th>
<th>Action</th>
</tr>
</thead>
<tbody class="list form-check-all">
</tbody>
</table>
...
<script>
$(document).ready(function() {
let dataTable = $('#table_id').DataTable({
processing: true,
serverSide: true,
autoWidth: false,
filter: false,
bLengthChange: false,
responsive: true,
language: {
processing: '<i class="fa fa-spinner fa-spin fa-3x fa-fw"></i><span class="sr-only">Loading...</span>'
},
ajax: {
url: "{{ url('/your route goes here') }}",
dataType: "json",
type: "GET",
data: function(d) {
d.filter_val = $('.search').val(),
d.search = $('input[type="search"]').val()
}
},
columns: [
{
data: "DT_RowIndex",
width: "3%"
}, {
data: "name"
}, {
data: "email"
}, {
data: "job_position"
}, {
data: "phone"
}, {
data: "action",
orderable: false,
searchable: false
}
]
});
});
</script>
Controller side - Method to send data
public function methodName(Request $request)
{
$data = ModelName::select('id', 'name', 'email', 'job_position', 'phone')->get();
if ( $request->ajax() ) {
return Datatables::of($data)
->addIndexColumn()
->addColumn('action', function ($row) {
return '<button href="" data-id="' . $row->id . '" onclick="edit(this)" class="btn btn-outline-info btn-icon waves-effect waves-light shadow-none btn-sm me-1" id="edit_contact" target="_blank"> <i class=" ri-pencil-fill"></i> </button>';
})->rawColumns(['action'])
->make(true);
}
return view('blade-file-name'));
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Hilmi Hidayat |
| Solution 2 | Samuel Hassid |