Codeigniter中DataTable服务端多表联查的分页过滤实现求助

Hey Krishna, let's sort out your multi-table DataTables server-side implementation! The core issues in your current code are that you're not applying DataTables' pagination, sorting, and filtering parameters to your join query, and your record count calculations are incorrect. Here's a step-by-step fix:

Key Issues in Your Current Code #

• You run a full join query first, then call $this->db->order_by() and $this->db->limit()—these don't affect your already-executed raw SQL.
• $totalData uses count_all() which returns the total rows in the default table (not your filtered join set).
• No handling for DataTables' global search/filtering.
• The sorting logic doesn't map to your joined table fields correctly.

Revised Controller Code #

public function get_form_ajax() {
    $requestData = $_REQUEST;
    // Map your DataTables columns to actual database fields (with table aliases)
    $columns = array(
        0 => 'transactions.id',
        1 => 'distributors.name',
        2 => 'distributors.code',
        3 => 'customers.firstname',
        4 => 'customers.mobile',
        5 => 'sales_offices.name',
        6 => 'MONTHNAME(transactions.payment_date)',
        7 => 'DATE(transactions.payment_date)',
        8 => 'transactions.balance_before',
        9 => '(CASE WHEN transactions.payment_type="Credit" THEN transactions.amount ELSE 0 END)',
        10 => '(CASE WHEN transactions.payment_type="Debit" THEN transactions.amount ELSE 0 END)',
        11 => 'transactions.balance_after'
    );

    // Initialize Active Record for join query
    $this->db->select('
        transactions.id as Id,
        distributors.name as CustomerName,
        distributors.code as CustomerNumber,
        customers.firstname as ConsumerName,
        customers.mobile as ConsumerNumber,
        sales_offices.name as ProfitCenter,
        MONTHNAME(transactions.payment_date) as Month,
        DATE(transactions.payment_date) as Date,
        transactions.balance_before as OpeningBalance,
        (CASE WHEN transactions.payment_type="Credit" THEN transactions.amount ELSE 0 END) as Receive,
        (CASE WHEN transactions.payment_type="Debit" THEN transactions.amount ELSE 0 END) as Sales,
        transactions.balance_after as ClosingBalance
    ')
    ->from('transactions')
    ->join('addresses', 'transactions.c_id = addresses.c_id')
    ->join('customers', 'customers.id = addresses.c_id')
    ->join('distributors', 'distributors.id = addresses.distributor_id')
    ->join('sales_offices', 'distributors.sales_office_id = sales_offices.id');

    // Apply base filter (sales_offices_id)
    if (!empty($sales_offices_id)) {
        $this->db->where('sales_offices.id', $sales_offices_id);
    }

    // Calculate total records (before filtering)
    $totalData = $this->db->count_all_results(false); // false preserves the query

    // Apply global search filter
    if (!empty($requestData['search']['value'])) {
        $searchValue = $requestData['search']['value'];
        $this->db->group_start()
            ->like('distributors.name', $searchValue)
            ->or_like('distributors.code', $searchValue)
            ->or_like('customers.firstname', $searchValue)
            ->or_like('customers.mobile', $searchValue)
            ->or_like('sales_offices.name', $searchValue)
            ->or_like('MONTHNAME(transactions.payment_date)', $searchValue)
            ->or_like('DATE(transactions.payment_date)', $searchValue)
            ->or_like('transactions.balance_before', $searchValue)
            ->or_like('transactions.amount', $searchValue)
            ->or_like('transactions.balance_after', $searchValue)
        ->group_end();
    }

    // Calculate filtered records count
    $totalFiltered = $this->db->count_all_results(false); // false preserves the query

    // Apply sorting
    if (isset($requestData['order'])) {
        $sortColumn = $columns[$requestData['order'][0]['column']];
        $sortDir = $requestData['order'][0]['dir'];
        $this->db->order_by($sortColumn, $sortDir);
    } else {
        // Default sort if no request
        $this->db->order_by('distributors.id', 'ASC')
                 ->order_by('customers.id', 'ASC')
                 ->order_by('transactions.id', 'ASC');
    }

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

    // Execute the final query
    $query = $this->db->get();
    $arr = $query->result_array();

    // Prepare response data
    $data = array();
    foreach ($arr as $key => $row) {
        $nestedData = array();
        $nestedData[] = $key + 1 + $requestData['start']; // Correct serial number with pagination
        $nestedData[] = $row['CustomerName'];
        $nestedData[] = $row['CustomerNumber'];
        $nestedData[] = $row['ConsumerName'];
        $nestedData[] = $row['ConsumerNumber'];
        $nestedData[] = $row['ProfitCenter'];
        $nestedData[] = $row['Month'];
        $nestedData[] = $row['Date'];
        $nestedData[] = $row['OpeningBalance'];
        $nestedData[] = $row['Receive'];
        $nestedData[] = $row['Sales'];
        $nestedData[] = $row['ClosingBalance'];
        $data[] = $nestedData;
    }

    // Build JSON response
    $json_data = array(
        "draw" => intval($requestData['draw']),
        "recordsTotal" => intval($totalData),
        "recordsFiltered" => intval($totalFiltered),
        "data" => $data
    );

    echo json_encode($json_data);
}

Small Adjustment to Your Ajax Code #

Note that your initial order setting targets column 0, which is the serial number in your frontend table (not the actual Id field). Update it to target a data column for proper default sorting:

table_datatable(page_limit){
    var dataTable = $('#myTable').DataTable( {
        "processing": true, // Enable processing indicator for better UX
        "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": [[ 7, "desc" ]], // Sort by Date column (index 7 in your frontend table)
        "destroy": true,
        "ajax":{
            url : "get_form_ajax",
            type: "post",
            error: function(){
                alert("Error loading data!");
            }
        }
    });
}

Why This Works #

• We use CodeIgniter's Active Record to build the join query, which makes it easy to dynamically add filters, sorting, and pagination.
• count_all_results(false) preserves the query after counting, so we don't have to rebuild it multiple times.
• The search logic targets all relevant fields across joined tables.
• The serial number calculation accounts for pagination offset, so it's correct on every page.

内容的提问来源于stack exchange，提问作者Krishna