i have the following code with only 2 records of data
my controller :
if (request()->ajax()){
$spareparts = Sparepart::select(DB::raw('DATE_FORMAT(spareparts.created_at,"%d-%m-%Y") as order_date'),
DB::raw("CONCAT(users.address, ', ', regencies.name, ', ', districts.name, ', ', provinces.name) as address"),
DB::raw("CONCAT(shipping_addresses.full_address, ', ', r.name, ', ', d.name, ', ', p.name) as shipping_address"),
DB::raw("GROUP_CONCAT(distinct sp.nama order by sp.id SEPARATOR ', ') as nama_sparepart"),
DB::raw("GROUP_CONCAT(distinct sp.model order by sp.id SEPARATOR ', ') as model"),
DB::raw("GROUP_CONCAT(distinct kategori_produks.nama SEPARATOR ', ') as nama"),
DB::raw("GROUP_CONCAT(distinct sp.quantity order by sp.id SEPARATOR ', ') as quantity"),
DB::raw("GROUP_CONCAT(distinct sp.foto SEPARATOR ', ') as foto"),
'spareparts.id', 'users.name', 'users.no_hp')
->leftJoin('sparepart_products as sp', 'spareparts.id', '=', 'sp.sparepart_id')
->leftJoin('users', 'spareparts.email', '=', 'users.email')
->leftJoin('shipping_addresses', 'users.email', '=', 'shipping_addresses.email')
->leftJoin('kategori_produks', 'sp.kategori_id', '=', 'kategori_produks.id')
->leftJoin('provinces', 'users.provinsi', '=', 'provinces.id')
->leftJoin('regencies', 'users.kota', '=', 'regencies.id')
->leftJoin('districts', 'users.kecamatan', '=', 'districts.id')
->leftJoin('provinces as p', 'shipping_addresses.provinsi', '=', 'p.id')
->leftJoin('regencies as r', 'shipping_addresses.kota', '=', 'r.id')
->leftJoin('districts as d', 'shipping_addresses.kecamatan', '=', 'd.id')
->where('spareparts.is_deleted', 0)
->groupBy('spareparts.id', 'shipping_addresses.full_address','r.name', 'd.name', 'p.name',
'regencies.name', 'districts.name', 'provinces.name')
->distinct();
if($request->filled('from_date') && $request->filled('to_date')) {
$spareparts = $spareparts->whereDate('spareparts.created_at', '>=', $request->from_date)->whereDate('spareparts.created_at', '<=', $request->to_date);
}
if(!empty($request->install_by)) {
$spareparts = $spareparts->where('kategori_produks.nama', $request->install_by);
}
return DataTables::of($spareparts)->addColumn('action', function ($id) {
if(Auth::user()->role == 'admin'){
return '<a href="sparepart-request/edit/' . $id->id . '" class="btn btn-primary">Edit</a>
<a href="sparepart-request/' . $id->id . '/delete" class="btn btn-danger">Delete</a>';
}
else{
return '<a href="sparepart-request/edit/' . $id->id . '" class="btn btn-primary">Edit</a>';
}
})->make();
}
my view :
<table id="tbl_sparepart" class="table table-striped table-bordered nowrap" cellspacing="0"
width="100%">
<thead>
<tr>
<th>Order Date</th>
<th>Name</th>
<th>Phone Number</th>
<th>Address</th>
<th>Shipping Address</th>
{{-- <th>Sparepart Name</th>
<th>Quantity</th>
<th>Category</th>
<th>Model</th>
<th>Sparepart Foto</th> --}}
<th>Action</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
var table_service = $('#tbl_sparepart').DataTable({
processing: true,
serverSide: true,
layout: {
topStart: {
'pageLength': 10,
buttons: [{
extend: 'csv',
text: 'Export Data',
filename: 'sparepart'
}]
}
},
scrollX: true,
ajax: {
url: '/sparepart-request',
data: function(d) {
d.from_date = $('input[name="daterange"]').data('daterangepicker').startDate
.format('YYYY-MM-DD');
d.to_date = $('input[name="daterange"]').data('daterangepicker').endDate.format(
'YYYY-MM-DD');
d.category_type = $('#category_type').val();
}
},
columns: [{
data: 'order_date',
name: 'order_date',
searchable: false
},
{
data: 'name',
name: 'users.name'
},
{
data: 'no_hp',
name: 'users.no_hp'
},
{
data: 'address',
name: 'users.address'
},
{
data: 'shipping_address',
name: 'shipping_address',
searchable: false
},
{
data: 'nama_sparepart',
name: 'sp.nama'
},
{
data: 'quantity',
name: 'sp.quantity'
},
{
data: 'nama',
name: 'kategori_produks.nama'
},
{
data: 'model',
name: 'sp.model'
},
{
data: 'foto',
name: 'sp.foto'
},
{
data: 'action',
name: 'action',
orderable: false,
searchable: false
},
]
});
and somehow it takes 10-30 seconds to load 2 records of data, but when i remove group_concat from my query, it makes the load time faster but i cant remove group_concat since i need it in my query.
i will appreciate any help, thanks in advance.
2
Answers
If the GROUP_CONCATs are definitely the culprit then you could consider having a dedicated index table which pre-builds all the concatenated strings into columns.
The index table would be a copy of the data from the other tables in your query above, but would already have the data formatted as required. You would have a script which can build this table at any time (could run every hour, for example to re-build).
You can then query this index table instead from the frontend for much faster querying.
There are so many things wrong here. Anyway, here’s an optimized version.
}
Things to consider to make it better.