In my previous code, I could use datatable with client side processing with such query:
Penulis::all();
but since there are huge amount of data exist, the load time is so long. so, I tried to use server side processing. here's my code:
The Table
<div class="table-responsive">
<table class="table table-striped" id="table_penulis">
<thead>
<tr>
<th>No</th>
<th>Aksi</th>
<th>namadepan</th>
<th>namatengah</th>
<th>namabelakang</th>
<th>gelardepan</th>
<th>gelarbelakang</th>
<th>jeniskelamin</th>
<th>tempatlahir</th>
<th>tanggallahir</th>
<th>jalan</th>
<th>kelurahan</th>
<th>rt</th>
<th>rw</th>
<th>kecamatan</th>
<th>kota</th>
<th>provinsi</th>
<th>kodepos</th>
<th>telp</th>
<th>email</th>
<th>bank</th>
<th>norek</th>
<th>namarek</th>
<th>pendidikanterakhir</th>
<th>pekerjaan</th>
<th>jabatan</th>
<th>namaperusahaan</th>
<th>jabatanfungsional</th>
<th>jabatanstruktural</th>
<th>prodi</th>
<th>jurusan</th>
<th>fakultas</th>
<th>linkedin</th>
<th>facebook</th>
<th>instagram</th>
<th>tiktok</th>
<th>namasaksi1</th>
<th>emailsaksi1</th>
<th>telpsaksi1</th>
<th>namasaksi2</th>
<th>emailsaksi2</th>
<th>telpsaksi2</th>
</tr>
</thead>
<tfoot>
<tr>
<th></th>
<th></th>
<th class="th">namadepan</th>
<th class="th">namatengah</th>
<th class="th">namabelakang</th>
<th class="th">gelardepan</th>
<th class="th">gelarbelakang</th>
<th class="th">jeniskelamin</th>
<th class="th">tempatlahir</th>
<th class="th">tanggallahir</th>
<th class="th">jalan</th>
<th class="th">kelurahan</th>
<th class="th">rt</th>
<th class="th">rw</th>
<th class="th">kecamatan</th>
<th class="th">kota</th>
<th class="th">provinsi</th>
<th class="th">kodepos</th>
<th class="th">telp</th>
<th class="th">email</th>
<th class="th">bank</th>
<th class="th">norek</th>
<th class="th">namarek</th>
<th class="th">pendidikanterakhir</th>
<th class="th">pekerjaan</th>
<th class="th">jabatan</th>
<th class="th">namaperusahaan</th>
<th class="th">jabatanfungsional</th>
<th class="th">jabatanstruktural</th>
<th class="th">prodi</th>
<th class="th">jurusan</th>
<th class="th">fakultas</th>
<th class="th">linkedin</th>
<th class="th">facebook</th>
<th class="th">instagram</th>
<th class="th">tiktok</th>
<th class="th">namasaksi1</th>
<th class="th">emailsaksi1</th>
<th class="th">telpsaksi1</th>
<th class="th">namasaksi2</th>
<th class="th">emailsaksi2</th>
<th class="th">telpsaksi2</th>
</tr>
</tfoot>
<tbody></tbody>
</table>
</div>
the Datatable (JQuery)
$('#table_penulis').DataTable({
processing : true,
serverSide : true,
responsive : true,
dom : 'Bfrtip',
paging : true,
ajax: "/api/master-penulis-datatable",
columns: [
{data : 'DT_RowIndex', name : 'DT_RowIndex', orderable: false, searchable: false},
{data : 'action', name : 'action', orderable: false, searchable: false},
{data : 'namadepan', name : 'namadepan' },
{data : 'namatengah', name : 'namatengah' },
{data : 'namabelakang', name : 'namabelakang' },
{data : 'gelardepan', name : 'gelardepan' },
{data : 'gelarbelakang', name : 'gelarbelakang' },
{data : 'jeniskelamin', name : 'jeniskelamin' },
{data : 'tempatlahir', name : 'tempatlahir' },
{data : 'tanggallahir', name : 'tanggallahir' },
{data : 'jalan', name : 'jalan' },
{data : 'kelurahan', name : 'kelurahan' },
{data : 'rt', name : 'rt' },
{data : 'rw', name : 'rw' },
{data : 'kecamatan', name : 'kecamatan' },
{data : 'kota', name : 'kota' },
{data : 'provinsi', name : 'provinsi' },
{data : 'kodepos', name : 'kodepos' },
{data : 'telp', name : 'telp' },
{data : 'email', name : 'email' },
{data : 'bank', name : 'bank' },
{data : 'norek', name : 'norek' },
{data : 'namarek', name : 'namarek' },
{data : 'pendidikanterakhir', name : 'pendidikanterakhir' },
{data : 'pekerjaan', name : 'pekerjaan' },
{data : 'jabatan', name : 'jabatan' },
{data : 'namaperusahaan', name : 'namaperusahaan' },
{data : 'jabatanfungsional', name : 'jabatanfungsional' },
{data : 'jabatanstruktural', name : 'jabatanstruktural' },
{data : 'prodi', name : 'prodi' },
{data : 'jurusan', name : 'jurusan' },
{data : 'fakultas', name : 'fakultas' },
{data : 'linkedin', name : 'linkedin' },
{data : 'facebook', name : 'facebook' },
{data : 'instagram', name : 'instagram' },
{data : 'tiktok', name : 'tiktok' },
{data : 'namasaksi1', name : 'namasaksi1' },
{data : 'emailsaksi1', name : 'emailsaksi1' },
{data : 'telpsaksi1', name : 'telpsaksi1' },
{data : 'namasaksi2', name : 'namasaksi2' },
{data : 'emailsaksi2', name : 'emailsaksi2' },
{data : 'telpsaksi2', name : 'telpsaksi2' },
],
initComplete: function() {
this.api().columns().every(function() {
var that = this;
$('input', this.footer()).on('keyup change clear', function() {
if (that.search() !== this.value) {
that.search(this.value).draw();
}
});
$('#table_penulis tfoot .th').each(function() {
var title = $(this).text();
$(this).html('<input type="text" class="form-control rounded shadow" placeholder="search" />');
});
});
},
});
and here's the Controller
$data = Penulis::query();
return $dataTables->eloquent($data)
->addIndexColumn()
->addColumn('action', function($row){
$btn = '<btn class="btn btn-info btn-sm" data-id="'. $row->id .'"><i class="bi bi-eye text-white"></i></btn>';
return $btn;
})
->rawColumns(['action'])
->toJson();
I'm using postgresql, here's the DDL:
CREATE TABLE "royalti"."writers" (
"namadepan" VARCHAR ( 50 ) COLLATE "pg_catalog"."default",
"namatengah" VARCHAR ( 50 ) COLLATE "pg_catalog"."default",
"namabelakang" VARCHAR ( 50 ) COLLATE "pg_catalog"."default",
"gelardepan" VARCHAR ( 20 ) COLLATE "pg_catalog"."default",
"gelarbelakang" VARCHAR ( 50 ) COLLATE "pg_catalog"."default",
"jeniskelamin" CHAR ( 1 ) COLLATE "pg_catalog"."default",
"tempatlahir" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"tanggallahir" DATE,
"jalan" VARCHAR ( 255 ) COLLATE "pg_catalog"."default",
"kelurahan" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"rt" VARCHAR ( 10 ) COLLATE "pg_catalog"."default",
"rw" VARCHAR ( 10 ) COLLATE "pg_catalog"."default",
"kecamatan" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"kota" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"provinsi" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"kodepos" VARCHAR ( 10 ) COLLATE "pg_catalog"."default",
"telp" VARCHAR ( 20 ) COLLATE "pg_catalog"."default",
"email" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"norek" VARCHAR ( 20 ) COLLATE "pg_catalog"."default",
"namarek" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"pendidikanterakhir" VARCHAR ( 10 ) COLLATE "pg_catalog"."default",
"pekerjaan" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"jabatan" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"namaperusahaan" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"jabatanfungsional" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"jabatanstruktural" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"prodi" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"jurusan" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"fakultas" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"linkedin" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"facebook" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"instagram" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"tiktok" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"namasaksi1" VARCHAR ( 255 ) COLLATE "pg_catalog"."default",
"emailsaksi1" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"telpsaksi1" VARCHAR ( 20 ) COLLATE "pg_catalog"."default",
"namasaksi2" VARCHAR ( 255 ) COLLATE "pg_catalog"."default",
"emailsaksi2" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
"telpsaksi2" VARCHAR ( 20 ) COLLATE "pg_catalog"."default",
"id" INT8 NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1 ),
"bank" VARCHAR ( 100 ) COLLATE "pg_catalog"."default",
CONSTRAINT "writers_pkey" PRIMARY KEY ( "id" ));
ALTER TABLE "royalti"."writers" OWNER TO "postgres";
The Client Side can perform individual column search, but since I'm using this type of serverside, the search doesn't work.
I Changed my Datatable JQuery to :
$(function() {
var table = $('#table_penulis').DataTable({
responsive: true,
dom: 'Bfrtip',
processing: true,
serverSide: true,
paginf: true,
ajax: '/api/master-penulis-datatable',
columns: [
{data: 'DT_RowIndex', name: 'DT_RowIndex', orderable: false, searchable: false},
{data: 'action', name: 'action', orderable: false, searchable: false},
{data: 'nama', name: 'nama'},
{data: 'jeniskelamin', name: 'jeniskelamin'},
{data: 'tempatlahir', name: 'tempatlahir'},
{data: 'tanggallahir', name: 'tanggallahir'},
{data: 'jalan', name: 'jalan'},
{data: 'kelurahan', name: 'kelurahan'},
{data: 'rt', name: 'rt'},
{data: 'rw', name: 'rw'},
{data: 'kecamatan', name: 'kecamatan'},
{data: 'kota', name: 'kota'},
{data: 'provinsi', name: 'provinsi'},
{data: 'kodepos', name: 'kodepos'},
{data: 'telp', name: 'telp'},
{data: 'email', name: 'email'},
{data: 'bank', name: 'bank'},
{data: 'norek', name: 'norek'},
{data: 'namarek', name: 'namarek'},
{data: 'pendidikanterakhir', name: 'pendidikanterakhir'},
{data: 'pekerjaan', name: 'pekerjaan'},
{data: 'jabatan', name: 'jabatan'},
{data: 'namaperusahaan', name: 'namaperusahaan'},
{data: 'jabatanfungsional', name: 'jabatanfungsional'},
{data: 'jabatanstruktural', name: 'jabatanstruktural'},
{data: 'prodi', name: 'prodi'},
{data: 'jurusan', name: 'jurusan'},
{data: 'fakultas', name: 'fakultas'},
{data: 'linkedin', name: 'linkedin'},
{data: 'facebook', name: 'facebook'},
{data: 'instagram', name: 'instagram'},
{data: 'tiktok', name: 'tiktok'},
{data: 'namasaksi1', name: 'namasaksi1'},
{data: 'emailsaksi1', name: 'emailsaksi1'},
{data: 'telpsaksi1', name: 'telpsaksi1'},
{data: 'namasaksi2', name: 'namasaksi2'},
{data: 'emailsaksi2', name: 'emailsaksi2'},
{data: 'telpsaksi2', name: 'telpsaksi2'},
],
initComplete: function() {
this.api()
.columns()
.every(function() {
var that = this;
$('input', this.footer()).on('keyup change clear', function() {
if (that.search() !== this.value) {
that.search(this.value).draw();
}
});
});
}
});
$('#table_penulis tfoot .th').each(function() {
var title = $(this).text();
$(this).html('<input type="text" class="form-control shadow" placeholder="search" />');
});
});