如何编写查询将同积分球员配对为单行并适配现有DataTables服务端代码

需求说明 #

目标是拉取已注册玩家数据，将积分相同的玩家配对合并为同一行展示，效果参考下文预期输出表。

原始玩家数据表 #

预期输出表 #

具体修改步骤 #

1. 修改HTML表头 #

新增匹配玩家对应的两列：

<table id="players" class="table table-bordered table-hover" cellspacing="0">
    <thead>
        <tr>
            <th>id</th>
            <th>player</th>
            <th>points</th>
            <th>player</th>
            <th>points</th>
        </tr>
    </thead>
    <tbody>
    </tbody>
</table>

2. 调整Model查询逻辑 #

替换原_get_datatables_query方法中的查询逻辑，用窗口函数实现同积分玩家排序配对（如果MySQL版本低于8.0不支持窗口函数，可替换为用户变量实现行号逻辑）：

private function _get_datatables_query()
{
    // 子查询：给同积分玩家按id排序打行号
    $subQuery = "SELECT id, player, points,
                ROW_NUMBER() OVER (PARTITION BY points ORDER BY id) as rn
                FROM {$this->table}";
    $this->db->from("($subQuery) as t");
    // 分组聚合，把同积分两行合并为一行
    $this->db->select('MIN(t.id) as id,
                MAX(CASE WHEN t.rn =1 THEN t.player END) as player1,
                MAX(CASE WHEN t.rn =1 THEN t.points END) as points1,
                MAX(CASE WHEN t.rn =2 THEN t.player END) as player2,
                MAX(CASE WHEN t.rn =2 THEN t.points END) as points2');
    $this->db->group_by('t.points');

    // 原有搜索逻辑调整，把新字段加入搜索范围
    $column_search = ['id', 'player1', 'points1', 'player2', 'points2'];
    $i = 0;
    foreach ($column_search as $item)
    {
        if($_POST['search']['value'])
        {
            if($i===0) $this->db->group_start();
            $this->db->or_like($item, $_POST['search']['value']);
            if(count($column_search) -1 == $i) $this->db->group_end();
        }
        $i++;
    }

    // 排序逻辑调整
    $column_order = [null,'id','player1','points1','player2','points2'];
    if(isset($_POST['order']))
    {
        $this->db->order_by($column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
    }else{
        $this->db->order_by('points1', 'asc');
    }
}

同时修改计数方法适配配对后的行数：

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

public function count_all()
{
    // 统计不同积分的组数，就是配对后的总行数
    return $this->db->distinct()->count_all_results($this->table, 'points');
}

3. 调整Controller数据组装逻辑 #

public function function()
{
    $list = $this->model->get_datatables();
    $data = array();
    $no = $_POST['start'];
    foreach ($list as $pair) {
        $no++;
        $row = array();
        $row[] = $no;
        $row[] = $pair->player1;
        $row[] = $pair->points1;
        $row[] = $pair->player2 ?? ''; // 奇数个同积分玩家时显示空
        $row[] = $pair->points2 ?? '';
        $data[] = $row;
    }
    $output = array(
        "draw" => $_POST['draw'],
        "recordsTotal" => $this->model->count_all(),
        "recordsFiltered" => $this->model->count_filtered(),
        "data" => $data,
    );
    echo json_encode($output);
}

4. 调整Datatables配置 #

Ajax代码无需修改，保持原有配置即可。

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