How to filter data-tables using two or more dependent drop-down list?

javascript jquery html datatables codeigniter-3
Ganesh Aher picture Ganesh Aher · Mar 27, 2018 · Viewed 7.8k times · Source

I'm trying to apply filters to my data-table with drop-down list box which are dependent. But when i'm trying to select a value from drop-down, data-table takes value from only one drop-down.

Here is my code:

Script:

<script type="text/javascript">
  $(document).ready(function(){
    var dataTable = $('#exampleProp').DataTable({
      "processing": true,
      "serverSide": true,
      "dom": 'lfrtip',
      "ajax": {
        "url": "<?= base_url('Property/fetchProp'); ?>",
        "dataType": "json",
        "type": "POST"
      },
      "lengthMenu": [[50, 100, 200, -1], [50, 100, 200, "All"]],
      // "ordering": false,
    });

    $('#areaId').on('change', function(){
      if (this.value == 1) {
        dataTable.search("Midlands & East of England").draw();
      } else {
        dataTable.search("North East, Yorkshire & Humberside").draw();
      }
    });
    $('#cluster_id').on('change', function(){
       dataTable.search(this.value).draw();   
    });
    $('#prop_type').on('change', function(){
       dataTable.search(this.value).draw();   
    });
    $('#prop_status').on('change', function(){
       dataTable.search(this.value).draw();   
    });

  });
</script>

In this, Cluster is dependent on Area, but if I select Area, it filters using area only, and not by cluster.

Here is the code to pick a cluster list from database:

$('#areaId').change(function(){
         var form_date =
         $.ajax({
          url: "<?= base_url('Property/clusterlistAddPropertyUse'); ?>",
          data: {areaId:$(this).val()},
          method:'POST',
          dataType: 'html',
          success:function(data){
              // $('#cluster_id option:selected').each(function(){
              // $(this).prop('selected', false);
              // });
              $('#cluster_id').html(data); 
              $('.propcluster').multiselect('rebuild');                 
             }
          }); 
    });

Here is my view code:

<?php if($this->session->flashdata('success_msg')){ ?>
  <div class="alert alert-success">
    <?php echo $this->session->flashdata('success_msg'); ?>
  </div> 
<?php } ?>

<?php if($this->session->flashdata('error_msg')){ ?>
  <div class="alert alert-danger">
    <?php echo $this->session->flashdata('error_msg'); ?>
  </div> 
<?php } ?>

<div class="panel panel-default" id="refresh">
  <div class="panel-heading">
    <b>Property List</b>
  </div>
  <div class="panel-body">
    <div class="col-md-3">
      <label>Area:</label>
      <select class="form-control select2" name="area_id" id="areaId">
        <option>All</option>
        <?php foreach ($areas as $area) { ?>
          <option value="<?= $area->area_id; ?>"><?php echo $area->area_name; ?></option>
        <?php } ?>
      </select>
    </div>
    <div class="col-md-3">
      <label>Cluster:</label>
      <select class="form-control select2" name="cluster_id[]" id="cluster_id">
      <option>All</option>
        <?php foreach ($clusters as $cluster){ ?>
          <option><?php echo $cluster->cluster_name; ?></option>
        <?php } ?>
      </select>
    </div>
    <div class="col-md-3">
      <label>Type:</label>
      <select class="form-control" name="property_type" id="prop_type">
        <option>All</option>
        <?php if ($property_type) { foreach ($property_type as $type) {?>
          <option><?= $type->property_type_name;?></option>
        <?php } } ?>
      </select> 
    </div> 
    <div class="col-md-3">
      <label>Stage:</label>
      <select class="form-control" name="property_status" id="prop_status">
        <option>All</option>   
        <?php foreach ($property_stage as $stage) { ?>  
          <option><?= $stage->stage_name; ?></option>
        <?php } ?>
      </select>
    </div> 
  </div>
  <div class="panel-body">
    <table id="exampleProp" class="table table-striped table-bordered" cellspacing="0" width="100%">
      <thead>
        <tr>
          <th>Code</th>
          <th>Date</th>
          <th>Type</th>
          <th>ASYS</th>
          <th>Address1</th>
          <!-- <th>Area</th> -->
          <th>City</th>
          <th>Status</th>
          <th>Landlord</th>
          <th>Rooms</th>
          <th>Edit</th>
          <th>Action</th>
        </tr>
      </thead>
      <tfoot>
        <tr>
          <th>Code</th>
          <th>Date</th>
          <th>Type</th>
          <th>ASYS No</th>
          <th>Address1</th>
          <!-- <th>Area</th> -->
          <th>City</th>
          <th>Status</th>
          <th>Landlord</th>
          <th>Rooms</th>
          <th>Edit</th>
          <th>Action</th>
        </tr>
      </tfoot>
    </table>
  </div>
  <div class="modal fade" id="myModal">
  <?php include('property_model_view.php'); ?>
  </div>
</div>

I want to filter data with both area and cluter and then type and stage also.

Edit: **

For more details, I'm adding Controller and model code here:

**

Model

public function prop_query()
{
    # code...
    $this->db->select('property_id, property_code, property_added_date, property_updated_date, property_type, tbl_property_type.property_type_name as type, property_ASYS_no, property_address_1, property_area, tbl_area.area_name as area, property_cluster, tbl_cluster.cluster_name as cluster, property_status, tbl_property_stage.stage_name as stage, property_landlord_id, concat(tbl_landlord.landlord_first_name, tbl_landlord.landlord_middle_name, tbl_landlord.landlord_last_name) as landlord, property_postcode, count(tbl_rooms.room_property_id) as rooms,');
    $this->db->from($this->property);

    $this->db->join('tbl_property_type', 'tbl_property.property_type = tbl_property_type.property_type_id', 'left');
    $this->db->join('tbl_area', 'tbl_property.property_area = tbl_area.area_id', 'left');
    $this->db->join('tbl_cluster', 'tbl_property.property_cluster = tbl_cluster.cluster_id', 'left');
    $this->db->join('tbl_property_stage', 'tbl_property.property_status = tbl_property_stage.stage_id', 'left');
    $this->db->join('tbl_landlord', 'tbl_property.property_landlord_id = tbl_landlord.landlord_id', 'left');
    $this->db->join('tbl_rooms', 'tbl_property.property_id = tbl_rooms.room_property_id', 'left');

    // $whereArray = array('tbl_property.property_type' => $propertyType, 'tbl_property.property_area' => $area, 'tbl_property.property_status' => $stageId, 'tbl_property.property_cluster' => '$clusterString');

    // $this->db->where('tbl_property.property_type', $propertyType);
    // $this->db->where('tbl_property.property_area', $area);
    // $this->db->where('tbl_property.property_status', $stageId);
    // $this->db->where('tbl_property.property_cluster', $clusterString);

    $this->db->group_by('tbl_property.property_id');
    // $this->db->order_by("tbl_property.property_updated_date", "DESC");

    if (isset($_POST["search"]["value"])) {
        # code...
        $this->db->like("property_id", $_POST["search"]["value"]);
        $this->db->or_like("property_code", $_POST["search"]["value"]);
        $this->db->or_like("property_added_date", $_POST["search"]["value"]);
        $this->db->or_like("tbl_property_type.property_type_name", $_POST["search"]["value"]);
        $this->db->or_like("property_ASYS_no", $_POST["search"]["value"]);
        $this->db->or_like("property_address_1", $_POST["search"]["value"]);
        $this->db->or_like("tbl_area.area_name", $_POST["search"]["value"]);
        $this->db->or_like("tbl_cluster.cluster_name", $_POST["search"]["value"]);
        $this->db->or_like("tbl_property_stage.stage_name", $_POST["search"]["value"]);
        $this->db->or_like("concat(tbl_landlord.landlord_first_name, tbl_landlord.landlord_middle_name, tbl_landlord.landlord_last_name)", $_POST["search"]["value"]);
        $this->db->or_like("property_postcode", $_POST["search"]["value"]);
    }

    if (isset($_POST["order"])) {
        # code...
        // $this->db->order_by("tbl_property.property_updated_date", "DESC");
        $this->db->order_by($this->order_column[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
    } else {
        # code...
        $this->db->order_by("tbl_property.property_updated_date", "DESC");
        // $this->db->order_by($this->order_column[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
    }
}

public function prop_datatables()
{
    # code...
    $this->prop_query();

    if ($_POST["length"] != -1) {
        # code...
        $this->db->limit($_POST["length"], $_POST["start"]);
    }

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

    return $query->result();
}

public function prop_filtered_data()
{
    # code...
    $this->prop_query();
    $query = $this->db->get();

    return $query->num_rows();
}

public function prop_all_data()
{
    # code...
    $this->db->select("*");
    $this->db->from($this->property);

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

Controller:

public function fetchProp()
{
    # code...
    $user = $this->ion_auth->user()->row();
    $data['username'] = $user->username;

    $data['user_id'] = $user->id;
    $user_id = $user->id;
    $data['groupId'] = $this->l->groupId($user_id);
    $data['group'] = $data['groupId']['0']->group_id;

    $fetch_prop = $this->pm->prop_datatables();

    $data = array();

    foreach ($fetch_prop as $row) {
        # code...
        $sub_array = array();
        $sub_array[] = $row->property_code;
        $sub_array[] = $row->property_added_date;
        $sub_array[] = $row->type;
        $sub_array[] = $row->property_ASYS_no;
        $sub_array[] = $row->property_address_1;
        // $sub_array[] = $row->area;
        $sub_array[] = $row->cluster;
        $sub_array[] = $row->stage;
        $sub_array[] = $row->landlord;
        $sub_array[] = $row->rooms;

            }
        }


        // $sub_array[] = '<a style="text-decoration: none;" href="'.base_url('Property/propertyDetails/'.$row->property_id).'" class="btn-warning btn-xs">View</a>&nbsp;
        // <a style="text-decoration: none;" href="'.base_url('Property/viewRoom/'.$row->property_id).'" class="btn-success btn-xs">Rooms</a>';


        $data[] = $sub_array;
    }

    $output = array(
        "draw" => intval($_POST["draw"]),
        "recordsTotal" => $this->pm->prop_all_data(),
        "recordsFiltered" => $this->pm->prop_filtered_data(),
        "data" => $data
    );

    echo json_encode($output);
}

I'm gone through this link Data-Tables, But it gives result from columns from table only, I'm not showing area column in the table.

Edit_2:

While googled, I got this link, Search API (regular expressions), Data table specific column filter with multi select drop down , Individual column searching (select inputs) , I'm trying to achieve result like this, But with Drop-down box.

Any kind of help is welcome. Thanks in advance.

Answer

Perran Mitchell picture Perran Mitchell · Apr 4, 2018

It is important to recognise the difference between .search() and .column().search()

One issue that you have here is that you're using .search() as if it is acting subtractively on the currently filtered set of data. In reality, each call of the .search() function simply runs on the original set of data, so it will only ever return a dataset with 1 filter applied.

To get around this, your best option is to search specific columns for specific values, and take advantage of the chaining available on .column().search()

I would suggest changing your approach to the dropdowns to use data attributes for the column that they wished to filter, for instance:

<select class="table-filter" name="area_id" data-column-filter="2">
    <option></option>
</select>
<select class="table-filter" name="cluster_id" data-column-filter="3">
    <option></option>
</select>

Where the data-column-filter attribute is the index of the column that you want to filter.

You can then write a simple event listener for changes on all relevant select boxes. We can even test the select box to see if it is a multi-select box, and react the event accordingly - we can get an array of the values chosen, and then join them together into a workable regex statement:

$('.table-filter').on('change',function(){
    $('.table-filter').each(function(){

        var filterColumn = $(this).data('column-filter');
        var filterValue = $(this).val();

        if($(this).is('[multiple]'])){
            var filterValuesExpression = filterValue.join('|');
            dataTable.column(filterColumn).search(filterValuesExpression, true, false );
        }else{
            dataTable.column(filterColumn).search(filterValue);
        }
    });

    dataTable.draw();
});

In terms of the logic you use to determine which boxes should and should not be part of the filter at any one time (which boxes are dependent on which others), this is quite separate from DataTables itself.

Related questions

JQuery .on('click') not working in DataTables 2nd page or rows past 11

I have a jQuery link that runs on a dynamic list for each row when the hyperlink is clicked. This works before datatables is applied, but once datatables is applied the 11th row (after changing display to higher than the …

Read More
How do I check whether a checkbox is checked in jQuery?

I need to check the checked property of a checkbox and perform an action based on the checked property using jQuery. For example, if the age checkbox is checked, then I need to show a textbox to enter age, else …

Read More
How can I know which radio button is selected via jQuery?

I have two radio buttons and want to post the value of the selected one. How can I get the value with jQuery? I can get all of them like this: $("form :radio") How do I know which one is …

Read More