How can I display data in Yajra datatables in Laravel 7?
I want to display data in Laravel using Yajra Datatables, but the problem is that the data can't synchronise 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 this case, the correct data is 4.
How to fix this issue? 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
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>
- Using preg_replace() to convert alphanumeric strings from camelCase to kebab-case
- Replace matched uppercase letter with its lowercase version (camelCase to kebab-case)
- Inject a comma between alphanumeric substrings and non-alphanumeric substrings
- I can't set Session Items in laravel inertia
- Isolate substring from first asterisk to second asterisk and use the first word of the match in the replacement
- PHP : ReflectionParameter, isOptional vs isDefaultValueAvailable
- Laravel Carbon subtract days from current date
- strlen() expects parameter 1 to be string, array given
- Not Acceptable! An appropriate representation of the requested resource could not be found on this server. This error was generated by Mod_Security
- Extract a file from a ZIP string
- How to always show the stock status in WooCommerce products
- Symfony Validator does not work with nested AtLeastOneOf Constraint
- TYPO3 extension: Is the __construct() of a class only called when the record is created via the FE, not the BE?
- Why must I rewind IteratorIterator
- How to update a chart in Google Slides dynamically?
- How to ensure full string validation with regex on phone number string?
- Password strength validating regex error: No ending delimiter or unknown modifier
- Regex error: Delimiter must not be alphanumeric or backslash
- How can I install a pecl extension like mcrypt in DDEV's web container?
- Match curly braced placeholders with a variable number of dot-delimited internal values
- Shorten long numbers to K/M/B?
- How to disable csrf in symfony?
- Match 3 subdirectory names in a filepath string after a specified directory name
- Validate the existence of banned words including near spellings in a string
- Get all <option> text values from an HTML document
- Get all matches between distinct substrings occurring on multiple lines
- Parse MSHTML and extract a <table> and it's contents
- Scrape href values from qualifying hyperlinks in Amazon's search results HTML
- Laravel 11 - HasMiddleware's middleware() method conflits with Illuminate\Routing\Controller's middlware()
- Scrape all numeric values from all qualifying list items in an HTML document