Codeigniter 中 Join Table 查询的 DataTable 服务器端处理

问：

我一直在尝试处理服务器端数据表，在处理单个表时我已经成功实现了数据表，但是现在我试图找出一种使用联接查询处理多个表的方法。我知道我必须提供 Limit、Order_by 和 filter 来对数据进行排序，但我有点困惑，无法将 Join Query 与数据表一起处理，因为我仍在学习和熟悉 Codeigniter 和数据表。我怎样才能限制和过滤使用此处的联接使用多个表获取的数据。任何帮助或建议都是高度赞赏的。这是我的控制器

function get_form_ajax() {
   $requestData= $_REQUEST;

   $columns = array( 
               0   =>  'Id',
               1   =>  'CustomerName',
               2   =>  'CustomerNumber',
               3   =>  'ConsumerName',
               4   =>  'ConsumerNumber',
               5   =>  'Profit Center',
               6   =>  'Month',
               7   =>  'Date',
               8   =>  'OpeningBalance',
               9   =>  'Recieve',
               10  =>  'Sales,
               11  =>  'ClosingBalance'
             );

   $where ="";

   if(!empty($sales_offices_id)){
     $where = "where sales_offices.id = '$sales_offices_id'";
   }
   else{
     $where .= "";
   }

   $sql = "select sales_offices.name as ProfitCenter, transactions.id as Id, distributors.name as CustomerName , distributors.code as CustomerNumber, customers.firstname as ConsumerName,customers.mobile as ConsumerNumber, customers.id as ConsumerId ,(case when transactions.payment_type='Credit' then transactions.amount else 0 end) as Receive, transactions.balance_before as OpeningBalance, (case when transactions.payment_type='Debit' then transactions.amount else 0 end) as Sales, transactions.balance_after as ClosingBalance,MONTHNAME(transactions.payment_date) as Month, Date(transactions.payment_date) as Date
           FROM `transactions`
           INNER JOIN addresses ON transactions.c_id = addresses.c_id 
           INNER JOIN customers ON customers.id = addresses.c_id
           INNER JOIN distributors ON distributors.id = addresses.distributor_id
           INNER JOIN sales_offices ON distributors.sales_office_id = sales_offices.id
           $where ORDER BY distributors.id , customers.id , transactions.id ASC";

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

   $arr = $query->result_array();

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

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

   $totalData = $this->db->count_all();

   $totalFiltered = $query->num_rows();

   $data = array();

   for( $i = 0 ; $i < count($arr) ; $i++ )
   {
     $nestedData=array();

     $nestedData[] = $i + 1;
     $nestedData[] = $arr[$i]['CustomerName'];
     $nestedData[] = $arr[$i]['CustomerNumber'];
     $nestedData[] = $arr[$i]['ConsumerName'];
     $nestedData[] = $arr[$i]['ConsumerNumber'];
     $nestedData[] = $arr[$i]['ProfitCenter'];
     $nestedData[] = $arr[$i]['Month'];
     $nestedData[] = $arr[$i]['Date'];
     $nestedData[] = $arr[$i]['OpeningBalance'];
     $nestedData[] = $arr[$i]['Receive'];
     $nestedData[] = $arr[$i]['Sales'];
     $nestedData[] = $arr[$i]['ClosingBalance'];

     $id = $arr[$i]['Id'];
     $customer_name    =   $arr[$i]['CustomerName'];
     $customer_no      =   $arr[$i]['CustomerNumber'];
     $consumer_name    =   $arr[$i]['ConsumerName'];
     $consumer_no      =   $arr[$i]['ConsumerNumber'];
     $proft_center     =   $arr[$i]['ProfitCenter'];
     $month            =   $arr[$i]['Month'];
     $date             =   $arr[$i]['Date'];
     $opening_bal      =   $arr[$i]['OpeningBalance'];
     $recieve          =   $arr[$i]['Receive'];
     $sales            =   $arr[$i]['Sales'];
     $closing_bal      =   $arr[$i]['ClosingBalance'];
     $data[] = $nestedData;
   }

   $json_data = array(
             "draw"            => intval( $requestData['draw'] ),   
             "recordsTotal"    => intval( $totalData ),
             "recordsFiltered" => intval( $totalFiltered ), 
             "data"            => $data
             );
   echo json_encode($json_data);
}

这是我的阿贾克斯

    function table_datatable(page_limit){
      var dataTable = $('#myTable').DataTable( {
        "processing": false,
        "serverSide": true,
        "dom": 'lBfrtip',
        "buttons": [
            {
                extend: 'collection',
                text: 'Export',
                buttons: [
                    'excel',
                    'pdf',
                    'print']
            }
        ],
        "lengthMenu": [[5, 10, 15, 20, 25, 30], [5, 10, 15, 20, 25, 30]],
        "pageLength": 10,
        "order": [[ 0, "desc" ]],
        "destroy": true,
        "ajax":{
            url : "get_form_ajax", // json datasource
            type: "post",  // method  , by default get
            error: function(){  // error handling

            }
        }
      });    
}