CodeIgniter 3 – Kombinasi Custom Filter, Datatables Server Side, dan Select 2

Setelah sebelumnya kita sudah membuat datatables server side pada artikel berjudul CodeIgniter 3 – Membuat Datatables Server Side, sekarang kita akan membuat custom filter dari data-data yang tampil dengan sedikit memodifikasi script pada artikel tersebut. Sekaligus kita juga akan mengaplikasikan Select 2 pada combo box-nya.

Langkah-langkah yang harus dilakukan adalah sebagai berikut:

Membuat Model

Buat model dengan nama Customfilter_model.php, kemudian ketikkan script dibawah:

<?php
defined('BASEPATH') or exit('No direct script access allowed');

class Customfilter_model extends CI_Model
{
    function __construct()
    {
        parent::__construct();
    }

    var $column_order = array(null, 'kelurahan.nama', 'kecamatan.nama', 'kabupaten.nama', 'provinsi.nama');
    var $column_search = array('kelurahan.nama', 'kecamatan.nama', 'kabupaten.nama',  'provinsi.nama');
    var $order = array('id_kel' => 'asc');

    private function _get_datatables_query()
    {
        //custom filter
        if ($this->input->post('provinsi')) {
            $this->db->where('provinsi.id_prov', $this->input->post('provinsi'));
        }
        if ($this->input->post('kabupaten')) {
            $this->db->where('kabupaten.id_kab', $this->input->post('kabupaten'));
        }
        if ($this->input->post('kecamatan')) {
            $this->db->where('kecamatan.id_kec', $this->input->post('kecamatan'));
        }

        $this->db->select('kelurahan.nama AS nm_kel, kecamatan.nama AS nm_kec, kabupaten.nama AS nm_kab, provinsi.nama AS nm_prov');
        $this->db->from('kelurahan');
        $this->db->join('kecamatan', 'kelurahan.id_kec = kecamatan.id_kec', 'left');
        $this->db->join('kabupaten', 'kecamatan.id_kab = kabupaten.id_kab', 'left');
        $this->db->join('provinsi', 'kabupaten.id_prov = provinsi.id_prov', 'left');

        $i = 0;

        foreach ($this->column_search as $item) {
            if ($_POST['search']['value']) {

                if ($i === 0) {
                    $this->db->group_start();
                    $this->db->like($item, $_POST['search']['value']);
                } else {
                    $this->db->or_like($item, $_POST['search']['value']);
                }

                if (count($this->column_search) - 1 == $i)
                    $this->db->group_end();
            }
            $i++;
        }

        if (isset($_POST['order'])) {
            $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        } else if (isset($this->order)) {
            $order = $this->order;
            $this->db->order_by(key($order), $order[key($order)]);
        }
    }

    function get_datatables()
    {
        $this->_get_datatables_query();
        if ($_POST["length"] != -1) {
            $this->db->limit($_POST['length'], $_POST['start']);
        }
        $query = $this->db->get();
        return $query->result();
    }

    public function count_filtered()
    {
        $this->_get_datatables_query();
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all()
    {
        $this->db->from('kelurahan');
        return $this->db->count_all_results();
    }

    function getprov($searchTerm = "")
    {
        $this->db->select('id_prov, nama');
        $this->db->where("nama like '%" . $searchTerm . "%' ");
        $this->db->order_by('id_prov', 'asc');
        $fetched_records = $this->db->get('provinsi');
        $dataprov = $fetched_records->result_array();

        $data = array();
        foreach ($dataprov as $prov) {
            $data[] = array("id" => $prov['id_prov'], "text" => $prov['nama']);
        }
        return $data;
    }

    function getkab($id_prov, $searchTerm = "")
    {
        $this->db->select('id_kab, nama');
        $this->db->where('id_prov', $id_prov);
        $this->db->where("nama like '%" . $searchTerm . "%' ");
        $this->db->order_by('id_kab', 'asc');
        $fetched_records = $this->db->get('kabupaten');
        $datakab = $fetched_records->result_array();

        $data = array();
        foreach ($datakab as $kab) {
            $data[] = array("id" => $kab['id_kab'], "text" => $kab['nama']);
        }
        return $data;
    }

    function getkec($id_kab, $searchTerm = "")
    {
        $this->db->select('id_kec, nama');
        $this->db->where('id_kab', $id_kab);
        $this->db->where("nama like '%" . $searchTerm . "%' ");
        $this->db->order_by('id_kec', 'asc');
        $fetched_records = $this->db->get('kecamatan');
        $datakec = $fetched_records->result_array();

        $data = array();
        foreach ($datakec as $kec) {
            $data[] = array("id" => $kec['id_kec'], "text" => $kec['nama']);
        }
        return $data;
    }

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

<?php

defined('BASEPATH') or exit('No direct script access allowed');

class Customfilter_model extends CI_Model

{

function __construct()

{

parent::__construct();

}

var $column_order = array(null, 'kelurahan.nama', 'kecamatan.nama', 'kabupaten.nama', 'provinsi.nama');

var $column_search = array('kelurahan.nama', 'kecamatan.nama', 'kabupaten.nama', 'provinsi.nama');

var $order = array('id_kel' => 'asc');

private function _get_datatables_query()

{

//custom filter

if ($this->input->post('provinsi')) {

$this->db->where('provinsi.id_prov', $this->input->post('provinsi'));

}

if ($this->input->post('kabupaten')) {

$this->db->where('kabupaten.id_kab', $this->input->post('kabupaten'));

}

if ($this->input->post('kecamatan')) {

$this->db->where('kecamatan.id_kec', $this->input->post('kecamatan'));

}

$this->db->select('kelurahan.nama AS nm_kel, kecamatan.nama AS nm_kec, kabupaten.nama AS nm_kab, provinsi.nama AS nm_prov');

$this->db->from('kelurahan');

$this->db->join('kecamatan', 'kelurahan.id_kec = kecamatan.id_kec', 'left');

$this->db->join('kabupaten', 'kecamatan.id_kab = kabupaten.id_kab', 'left');

$this->db->join('provinsi', 'kabupaten.id_prov = provinsi.id_prov', 'left');

$i = 0;

foreach ($this->column_search as $item) {

if ($_POST['search']['value']) {

if ($i === 0) {

$this->db->group_start();

$this->db->like($item, $_POST['search']['value']);

} else {

$this->db->or_like($item, $_POST['search']['value']);

}

if (count($this->column_search) - 1 == $i)

$this->db->group_end();

}

$i++;

}

if (isset($_POST['order'])) {

$this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);

} else if (isset($this->order)) {

$order = $this->order;

$this->db->order_by(key($order), $order[key($order)]);

}

function get_datatables()

{

$this->_get_datatables_query();

if ($_POST["length"] != -1) {

$this->db->limit($_POST['length'], $_POST['start']);

}

$query = $this->db->get();

return $query->result();

}

public function count_filtered()

{

$this->_get_datatables_query();

$query = $this->db->get();

return $query->num_rows();

}

public function count_all()

{

$this->db->from('kelurahan');

return $this->db->count_all_results();

}

function getprov($searchTerm = "")

{

$this->db->select('id_prov, nama');

$this->db->where("nama like '%" . $searchTerm . "%' ");

$this->db->order_by('id_prov', 'asc');

$fetched_records = $this->db->get('provinsi');

$dataprov = $fetched_records->result_array();

$data = array();

foreach ($dataprov as $prov) {

$data[] = array("id" => $prov['id_prov'], "text" => $prov['nama']);

}

return $data;

}

function getkab($id_prov, $searchTerm = "")

{

$this->db->select('id_kab, nama');

$this->db->where('id_prov', $id_prov);

$this->db->where("nama like '%" . $searchTerm . "%' ");

$this->db->order_by('id_kab', 'asc');

$fetched_records = $this->db->get('kabupaten');

$datakab = $fetched_records->result_array();

$data = array();

foreach ($datakab as $kab) {

$data[] = array("id" => $kab['id_kab'], "text" => $kab['nama']);

}

return $data;

}

function getkec($id_kab, $searchTerm = "")

{

$this->db->select('id_kec, nama');

$this->db->where('id_kab', $id_kab);

$this->db->where("nama like '%" . $searchTerm . "%' ");

$this->db->order_by('id_kec', 'asc');

$fetched_records = $this->db->get('kecamatan');

$datakec = $fetched_records->result_array();

$data = array();

foreach ($datakec as $kec) {

$data[] = array("id" => $kec['id_kec'], "text" => $kec['nama']);

}

return $data;

}

See also Pivot Table di CodeIgniter

Membuat Controller

Buat controller dengan nama Customfilter.php, dan ketikkan script di bawah:

<?php
defined('BASEPATH') or exit('No direct script access allowed');

class Customfilter extends CI_Controller
{

    public function __construct()
    {
        parent::__construct();
        $this->load->model('Customfilter_model', 'Custom');
    }

    public function index()
    {
        $this->load->view('customfilter');
    }

    public function listdata()
    {
        $list = $this->Custom->get_datatables();
        $data = array();
        $no   = $_POST['start'];
        if (isset($_POST['start']) && isset($_POST['draw'])) {
            $no = $_POST['start'];
        } else {
            die();
        };

        foreach ($list as $dt) {
            $no++;
            $row   = array();
            $row[] = $no;
            $row[] = $dt->nm_kel;
            $row[] = $dt->nm_kec;
            $row[] = $dt->nm_kab;
            $row[] = $dt->nm_prov;
            $data[] = $row;
        }

        $output = array(
            "draw"            => $_POST['draw'],
            "recordsTotal"    => $this->Custom->count_all(),
            "recordsFiltered" => $this->Custom->count_filtered(),
            "data"            => $data,
        );
        echo json_encode($output);
    }

    // Provinsi
    public function getdataprov()
    {
        $searchTerm = $this->input->post('searchTerm');
        $response   = $this->Custom->getprov($searchTerm);
        echo json_encode($response);
    }

    // Kabupaten
    public function getdatakab($id_prov)
    {
        $searchTerm = $this->input->post('searchTerm');
        $response   = $this->Custom->getkab($id_prov, $searchTerm);
        echo json_encode($response);
    }

    // Kecamatan
    public function getdatakec($id_kab)
    {
        $searchTerm = $this->input->post('searchTerm');
        $response   = $this->Custom->getkec($id_kab, $searchTerm);
        echo json_encode($response);
    }

}

<?php

defined('BASEPATH') or exit('No direct script access allowed');

class Customfilter extends CI_Controller

{

public function __construct()

{

parent::__construct();

$this->load->model('Customfilter_model', 'Custom');

}

public function index()

{

$this->load->view('customfilter');

}

public function listdata()

{

$list = $this->Custom->get_datatables();

$data = array();

$no = $_POST['start'];

if (isset($_POST['start']) && isset($_POST['draw'])) {

$no = $_POST['start'];

} else {

die();

};

foreach ($list as $dt) {

$no++;

$row = array();

$row[] = $no;

$row[] = $dt->nm_kel;

$row[] = $dt->nm_kec;

$row[] = $dt->nm_kab;

$row[] = $dt->nm_prov;

$data[] = $row;

}

$output = array(

"draw" => $_POST['draw'],

"recordsTotal" => $this->Custom->count_all(),

"recordsFiltered" => $this->Custom->count_filtered(),

"data" => $data,

);

echo json_encode($output);

}

// Provinsi

public function getdataprov()

{

$searchTerm = $this->input->post('searchTerm');

$response = $this->Custom->getprov($searchTerm);

echo json_encode($response);

}

// Kabupaten

public function getdatakab($id_prov)

{

$searchTerm = $this->input->post('searchTerm');

$response = $this->Custom->getkab($id_prov, $searchTerm);

echo json_encode($response);

}

// Kecamatan

public function getdatakec($id_kab)

{

$searchTerm = $this->input->post('searchTerm');

$response = $this->Custom->getkec($id_kab, $searchTerm);

echo json_encode($response);

}

Membuat View

Buat view dengan nama customfilter.php, kemudian ketikkan script di bawah:

<!DOCTYPE html>
<html>

<head>
    <title>Codeigniter - Custom Filter Datatables Server Side</title>

    <!-- Font -->
    <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=Oswald:wght@300;400;600&display=swap" rel="stylesheet">

    <!-- Bootstrap CSS -->
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.6.0/dist/css/bootstrap.min.css" integrity="sha384-B0vP5xmATw1+K9KRQjQERJvTumQW0nPEzvF6L/Z6nronJ3oUOFUFpCjEUQouq2+l" crossorigin="anonymous">

    <!-- JQuery -->
    <script src="https://code.jquery.com/jquery-3.3.1.min.js" integrity="sha256-FgpCb/KJQlLNfOu91ta32o/NMZxltwRo8QtmkMRdAu8=" crossorigin="anonymous"></script>

    <!-- Datatables -->
    <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css">
    <script type="text/javascript" language="javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>

    <!-- Select2 -->
    <link href="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/css/select2.min.css" rel="stylesheet" />
    <script src="https://cdn.jsdelivr.net/npm/select2@4.1.0-rc.0/dist/js/select2.min.js"></script>

    <style>
        body {
            font-family: 'Oswald', sans-serif;
            font-weight: 300;
        }
    </style>
</head>

<body>
    <div class="container">
        <div class="row mt-4">
            <div class="col-12">
                <div class="col-md-6">
                    <h3>Custom Filter Datatables Serverside</h3>
                    <form id="form-filter">
                        <div class="form-group">
                            <label for="provinsi">Provinsi</label>
                            <select id="provinsi" name="provinsi" class="form-control select2">
                                <option value="" selected>Pilih Provinsi</option>
                            </select>
                        </div>
                        <div class="form-group">
                            <label for="kabupaten">Kabupaten</label>
                            <select id="kabupaten" name="kabupaten" class="form-control select2">
                                <option value="" selected>Pilih Kabupaten</option>
                            </select>
                        </div>
                        <div class="form-group">
                            <label for="kecamatan">Kecamatan</label>
                            <select id="kecamatan" name="kecamatan" class="form-control select2">
                                <option value="" selected>Pilih kecamatan</option>
                            </select>
                        </div>
                        <div class="form-group">
                            <button type="button" id="btn-reset" class="btn btn-sm elevation-1 btn-info"><i class="fas fa-sync-alt"></i> Reset</button>
                            <button type="button" id="btn-filter" class="btn btn-sm elevation-1 btn-danger"><i class="fas fa-search"></i> Filter</button>
                        </div>

                    </form>
                </div>
            </div>
        </div>
        <div class="row mt-4">
            <div class="col-12">
                <div class="card">
                    <div class="card-header">
                        DAFTAR KELURAHAN
                    </div>
                    <div class="card-body">
                        <div class="responsive">
                            <table id="list_data" class="table display row-border table-striped compact" style="width: 100%;">
                                <thead>
                                    <tr>
                                        <th>No</th>
                                        <th>Kelurahan</th>
                                        <th>Kecamatan</th>
                                        <th>Kabupaten</th>
                                        <th>Provinsi</th>
                                    </tr>
                                </thead>
                            </table>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>
    <script type="text/javascript">
        // Provinsi
        $(document).ready(function() {
            $("#provinsi").select2({
                ajax: {
                    url: '<?= base_url() ?>customfilter/getdataprov',
                    type: "post",
                    dataType: 'json',
                    delay: 200,
                    data: function(params) {
                        return {
                            searchTerm: params.term
                        };
                    },
                    processResults: function(response) {
                        return {
                            results: response
                        };
                    },
                    cache: true
                }
            });
        });

        // Kabupaten
        $("#provinsi").change(function() {
            var id_prov = $("#provinsi").val();
            $("#kabupaten").select2({
                ajax: {
                    url: '<?= base_url() ?>customfilter/getdatakab/' + id_prov,
                    type: "post",
                    dataType: 'json',
                    delay: 200,
                    data: function(params) {
                        return {
                            searchTerm: params.term
                        };
                    },
                    processResults: function(response) {
                        return {
                            results: response
                        };
                    },
                    cache: true
                }
            });
        });

        // Kecamatan
        $("#kabupaten").change(function() {
            var id_kab = $("#kabupaten").val();
            $("#kecamatan").select2({
                ajax: {
                    url: '<?= base_url() ?>customfilter/getdatakec/' + id_kab,
                    type: "post",
                    dataType: 'json',
                    delay: 200,
                    data: function(params) {
                        return {
                            searchTerm: params.term
                        };
                    },
                    processResults: function(response) {
                        return {
                            results: response
                        };
                    },
                    cache: true
                }
            });
        });

        var dataTable_;
        $(document).ready(function() {
            dataTable_ = $('#list_data').DataTable({
                processing: true,
                serverSide: true,
                searching: true,
                info: true,
                paging: true,
                lengthChange: true,
                ordering: true,
                ajax: {
                    url: '<?php echo base_url('customfilter/listdata'); ?>',
                    type: "POST",
                    data: function(data) {
                        data.provinsi = $('#provinsi').val();
                        data.kabupaten = $('#kabupaten').val();
                        data.kecamatan = $('#kecamatan').val();
                    },
                },
            });
        })

        $('#btn-filter').click(function() { //button filter event click
            dataTable_.ajax.reload(); //just reload table
        });
        $('#btn-reset').click(function() { //button reset event click
            $('#provinsi').val('').trigger('change')
            $('#kabupaten').val('').trigger('change')
            $('#kecamatan').val('').trigger('change')
            $('#form-filter')[0].reset();
            dataTable_.ajax.reload(); //just reload table
        });
    </script>
</body>

</html>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

<!DOCTYPE html>

<html>

<head>

<title>Codeigniter - Custom Filter Datatables Server Side</title>

<style>

body {

font-family: 'Oswald', sans-serif;

font-weight: 300;

}

</style>

</head>

<body>

<h3>Custom Filter Datatables Serverside</h3>

<label for="provinsi">Provinsi</label>

<option value="" selected>Pilih Provinsi</option>

</select>

</div>

<label for="kabupaten">Kabupaten</label>

<option value="" selected>Pilih Kabupaten</option>

</select>

</div>

<label for="kecamatan">Kecamatan</label>

<option value="" selected>Pilih kecamatan</option>

</select>

</div>

<button type="button" id="btn-reset" class="btn btn-sm elevation-1 btn-info"><i class="fas fa-sync-alt"></i> Reset</button>

<button type="button" id="btn-filter" class="btn btn-sm elevation-1 btn-danger"><i class="fas fa-search"></i> Filter</button>

</div>

</form>

</div>

DAFTAR KELURAHAN

</div>

<thead>

<tr>

<th>Kelurahan</th>

<th>Kecamatan</th>

<th>Kabupaten</th>

<th>Provinsi</th>

</tr>

</thead>

</table>

</div>

// Provinsi

$(document).ready(function() {

$("#provinsi").select2({

ajax: {

url: '<?= base_url() ?>customfilter/getdataprov',

type: "post",

dataType: 'json',

delay: 200,

data: function(params) {

return {

searchTerm: params.term

};

processResults: function(response) {

return {

results: response

};

cache: true

}

});

// Kabupaten

$("#provinsi").change(function() {

var id_prov = $("#provinsi").val();

$("#kabupaten").select2({

ajax: {

url: '<?= base_url() ?>customfilter/getdatakab/' + id_prov,

type: "post",

dataType: 'json',

delay: 200,

data: function(params) {

return {

searchTerm: params.term

};

processResults: function(response) {

return {

results: response

};

cache: true

}

});

// Kecamatan

$("#kabupaten").change(function() {

var id_kab = $("#kabupaten").val();

$("#kecamatan").select2({

ajax: {

url: '<?= base_url() ?>customfilter/getdatakec/' + id_kab,

type: "post",

dataType: 'json',

delay: 200,

data: function(params) {

return {

searchTerm: params.term

};

processResults: function(response) {

return {

results: response

};

cache: true

}

});

var dataTable_;

$(document).ready(function() {

dataTable_ = $('#list_data').DataTable({

processing: true,

serverSide: true,

searching: true,

info: true,

paging: true,

lengthChange: true,

ordering: true,

ajax: {

url: '<?php echo base_url('customfilter/listdata'); ?>',

type: "POST",

data: function(data) {

data.provinsi = $('#provinsi').val();

data.kabupaten = $('#kabupaten').val();

data.kecamatan = $('#kecamatan').val();

});

})

$('#btn-filter').click(function() { //button filter event click

dataTable_.ajax.reload(); //just reload table

});

$('#btn-reset').click(function() { //button reset event click

$('#provinsi').val('').trigger('change')

$('#kabupaten').val('').trigger('change')

$('#kecamatan').val('').trigger('change')

$('#form-filter')[0].reset();

dataTable_.ajax.reload(); //just reload table

});

</script>

</body>

</html>

Jika langkah-langkah di atas sudah sesuai maka akan didapatkan hasil seperti berikut ini

Nah, demikian bahasan kita kali ini tentang custom filter, datatables server side, dan select 2. Jika ada yang kurang jelas silahkan isi kolom komentar. Terimakasih.

CodeIgniter 3 – Kombinasi Custom Filter, Datatables Server Side, dan Select 2

1 Comment

Leave a Reply Cancel reply

CATEGORIES

ARCHIVES

Statistik

QUOTES

CodeIgniter 3 – Kombinasi Custom Filter, Datatables Server Side, dan Select 2

YOU MIGHT BE INTERESTED IN

Membuat Button Export dan Print pada Datatables Serverside

CodeIgniter 3 – Membuat Datatables Server Side

1 Comment

Leave a Reply Cancel reply

CATEGORIES

ARCHIVES

Statistik

QUOTES