jquery - 数据表分页搜索框不起作用

Question

我正在使用 codeigniter，并且在代码中使用数据表分页进行分页，但搜索框不起作用

查看:

<table id="loc_vh_frt_table" class="table table-bordered table-striped table-sm" >
  <thead>
  <tr>
    <th>SR No</th>
    <th>Local Vehicle Freight No</th>
    <th>Date</th>
    <th>&nbsp;&nbsp;&nbsp;Update/Disable&nbsp;&nbsp;&nbsp;</th>
  </tr>
  </thead>
</table>

<script>
  $(document).ready(function(){
   var table = $('#loc_vh_frt_table').DataTable({
          "processing": true,
          "serverSide": true,
           scrollY    : "500px",
           scrollX    : true,
           scrollCollapse: true,
          "order": [],

          "ajax": {
              "url": "<?php echo base_url('booking/local_vehicle_freight/CLocalVehicleFreight/getLists/'); ?>",
              "type": "POST"
          },
          "columnDefs": [{ 
              "targets": [0],
              "orderable": false

          }],
          "columnDefs": [ {
            "targets": [9],
            "data": null, 
            "defaultContent": "<button class=\"btn btn-success btn1 btn-sm\" >UPDATE</button> <button class=\"btn btn-success btn2 btn-sm\" >DISABLE</button>"
          }]
      });

        $('#loc_vh_frt_table tbody').on( 'click', 'button.btn1', function () {
        var data = table.row( $(this).parents('tr') ).data();
        $.redirect("<?php echo base_url(); ?>booking/local_vehicle_freight/CLocalVehicleFreight/updateFreightMemo?loc_truck_id="+data[1], "POST"); 
    });
       $('#loc_vh_frt_table tbody').on( 'click', 'button.btn2', function () {
       var data = table.row( $(this).parents('tr') ).data();
       disable_freight(data[1]);
    });
  });
</script> 

Controller :

function getLists(){
      $data = $row = array();

     $memData = $this->localFreightModel->getRows($_POST);

    $i = $_POST['start'];
    foreach($memData as $loc_vh){
        $i++;
        $data[] = array( $i,
                         $loc_vh->loc_truck_ids,
                         $loc_vh->local_truck_date,
                         null);
    }
    $output = array(
        "draw" => $_POST['draw'],
        "recordsTotal"    => $this->localFreightModel->countAll(),
        "recordsFiltered" => $this->localFreightModel->countFiltered($_POST),
        "data" => $data,
    );
    echo json_encode($output);
}

型号:

function __construct() {

        $this->table = 'local_truck_freight l';
        $this->column_order = array(null, 'l.loc_truck_ids','l.local_truck_date','`bn.branch_name as from_branch`','`bn1.branch_name as to`','`vn.vehicle_no as vehicle`','l.loc_truck_add','loc_truck_remark','l.loc_pay_type');

        $this->column_search = array('l.loc_truck_ids','l.local_truck_date','`bn.branch_name as from_branch`','`bn1.branch_name as to`','`vn.vehicle_no as vehicle`','l.loc_truck_add','loc_truck_remark','l.loc_pay_type');
        $this->order = array('l.loc_truck_ids' => 'asc');
    }
    public function getRows($postData){

        $this->db->select('l.loc_truck_ids,l.local_truck_date,`bn.branch_name as from_branch`,`bn1.branch_name as to`,`vn.vehicle_no as vehicle`,l.loc_truck_add,loc_truck_remark,l.loc_pay_type');
         $this->_get_datatables_query($postData);
        if($postData['length'] != -1){
            $this->db->limit($postData['length'], $postData['start']);

            $this->db->join('vehicle vn', 'l.loc_truck_lorryno=vn.vehicle_id','left');
            $this->db->join('branch  bn', 'l.local_truck_from=bn.branch_id','left');
            $this->db->join('branch  bn1','l.loc_truck_to=bn1.branch_id','left');

            $this->db->where('l.local_truck_from',$this->session->userdata('user_branch'));
            $this->db->where('l.status','active');

        }

        $query = $this->db->get();
        //return $query->result();
        return $query->result();
    }


    public function countAll(){
        $this->db->from($this->table);
        return $this->db->count_all_results();
    }


    public function countFiltered($postData){
        $this->_get_datatables_query($postData);

        $query = $this->db->get();
        return $query->num_rows();
    }


    private function _get_datatables_query($postData){

        $this->db->from($this->table);

        $i = 0;

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

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

                if($i===0){

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

                }else{
                    $this->db->or_like($item, $postData['search']['value']);

                }


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

                    $this->db->group_end();
                }
            }
            $i++;
        }

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

这段代码的错误是:

Unknown column 'bn.branch_name as from_branch' in 'where clause'

SELECT `l`.`loc_truck_ids`, `l`.`local_truck_date`, `bn`.`branch_name` as `from_branch`, `bn1`.`branch_name` as `to`, `vn`.`vehicle_no` as `vehicle`, `l`.`loc_truck_add`, `loc_truck_remark`, `l`.`loc_pay_type` FROM `local_truck_freight` `l` LEFT JOIN `vehicle` `vn` ON `l`.`loc_truck_lorryno`=`vn`.`vehicle_id` LEFT JOIN `branch` `bn` ON `l`.`local_truck_from`=`bn`.`branch_id` LEFT JOIN `branch` `bn1` ON `l`.`loc_truck_to`=`bn1`.`branch_id` WHERE ( l.loc_truck_ids LIKE '%p%' ESCAPE '!' OR l.local_truck_date LIKE '%p%' ESCAPE '!' OR `bn.branch_name as from_branch` LIKE '%p%' ESCAPE '!' OR `bn1.branch_name as to` LIKE '%p%' ESCAPE '!' OR `vn.vehicle_no as vehicle` LIKE '%p%' ESCAPE '!' OR l.loc_truck_add LIKE '%p%' ESCAPE '!' OR loc_truck_remark LIKE '%p%' ESCAPE '!' OR l.loc_pay_type LIKE '%p%' ESCAPE '!' ) AND `l`.`local_truck_from` = '1' AND `l`.`status` = 'active' ORDER BY `l`.`loc_truck_ids` ASC LIMIT 10

Filename: models/booking/local_vehicle_freight/MLocalVehicleFreight.php

Line Number: 37

在上面的代码中，我上传了我的模型 View 和 Controller 。我无法准确地了解我的代码中错误的地方。它执行获取数据、排序数据和分页，但它在我的搜索框中给出错误。

Answer 1

//js

var dtable = $('#loc_vh_frt_table').DataTable({
            processing: true,
            serverSide: false,
            searching: true,
            dom: 'lBfrtip',
            lengthMenu: [[5, 10, 25, 50, 100, -1], [5, 10, 25, 50, 100, "All"]],
            pageLength: 10,
            buttons: [
                'csv', 'print'
            ],
            ajax: {
                "url": base_url + "/controller_name/datatables",
                'method': 'POST',
                'data': function (d) {
                    d._token = $("input[name=_token]").val();
                }
            }

//controller

 function index() {
        $data = array();
        $data['view'] = 'view_page_name';
      $this->load->view('header', $data);
    }
     function datatables() {
            $data = array();
            $stock =$this->Stock_model->getRows($this->input->post());
            parent::json_output(["draw" => intval($this->input->post('draw')), "recordsTotal" => $stock[1], "recordsFiltered" =>$stock[1], "data" => $stock[0]]);
            return;
        }

//型号

function getRows($requestArray) {

        if (isset($requestArray['keyword']) && $requestArray['keyword'] != '') {
            $this->db->where .= " AND (field_name_by_which_you_want_search LIKE '%" . $requestArray['keyword'] . "%')";
            $this->db->where .= " AND (field_name_by_which_you_want_search LIKE '%" . $requestArray['keyword'] . "%')";
        }
        $this->db->where($where);
        if ($this->input->post('length') != '-1') {
            $this->db->limit($this->input->post('length'), $this->input->post('start'));
        }
 $this->db->order_by('id','desc');
        $query = $result->get('table_name')->result_array();

        $count = $result->get('table_name')->num_rows();
        return [$query, $count];
    }