CodeIgniter中如何按类别正确展示候选人评分数据？

我正在开发一款展示候选人得分的评分制表Web应用，尝试把数据放到对应类别列中，但始终失败。已经用了distinct和group by语句，问题还是存在。

当前问题表现 #

截图显示：每个候选人对应多行，每行仅展示一个类别的得分，无法将同一候选人的不同类别得分整合到同一行的对应列中。

期望实现的表格效果 #

当前代码 #

Model代码 #

public function get_score_candidates(){
    $this->db->distinct();
    $this->db->select('categories.description,candidates.fullname,categories.percent');
    $this->db->from('tabulations');  
    $this->db->join('categories','categories.cat_id = tabulations.cat_id');
    $this->db->join('candidates','candidates.cand_id = tabulations.cand_id');
    $query = $this->db->get();
    return $query->result_array();
}

public function get_scores(){
    $this->db->distinct();
    $this->db->select('score');
    $this->db->from('tabulations');  
    $query = $this->db->get();
    return $query->result_array();
}

View代码 #

<thead class="text-center">
    <tr>
        <th></th>
        <?php foreach($scores as $score): ?>
            <th scope="col"><?php echo $score['description'].' ('.$score['percent'].'%)' ?></th>
        <?php endforeach; ?>
    </tr>
</thead>
<tbody class="text-center">
    <?php foreach($scores as $score): ?>

        <tr> 
            
            <td><?php echo $score['fullname'] ?></td>
            <?php foreach($cand_scores as $cand_score): ?>
                
                <td><input type="number" name="score[]" class="form-control" value="<?php echo $cand_score['score'] ?>"></td>

            <?php endforeach; ?>
        </tr>
    <?php endforeach; ?>
</tbody>

解决方案 #

1. 重构Model数据结构 #

当前查询返回扁平数据，需先整理成「候选人->类别得分」的嵌套结构，适配表格渲染逻辑：

public function get_candidate_scores() {
    // 获取所有类别信息（用于生成表头）
    $categories = $this->db->get('categories')->result_array();
    
    // 获取所有候选人的得分原始数据
    $this->db->select('candidates.fullname, categories.description, tabulations.score');
    $this->db->from('tabulations');
    $this->db->join('categories', 'categories.cat_id = tabulations.cat_id');
    $this->db->join('candidates', 'candidates.cand_id = tabulations.cand_id');
    $raw_data = $this->db->get()->result_array();
    
    // 整理数据：以候选人为键，存储其对应各类别的得分
    $candidate_scores = [];
    foreach ($raw_data as $item) {
        $name = $item['fullname'];
        if (!isset($candidate_scores[$name])) {
            $candidate_scores[$name] = [];
        }
        $candidate_scores[$name][$item['description']] = $item['score'];
    }
    
    return [
        'categories' => $categories,
        'candidate_scores' => $candidate_scores
    ];
}

2. 修改View渲染逻辑 #

基于整理后的嵌套数据，按类别顺序匹配对应得分：

<?php 
$categories = $data['categories'];
$candidate_scores = $data['candidate_scores'];
?>
<thead class="text-center">
    <tr>
        <th></th>
        <?php foreach($categories as $cat): ?>
            <th scope="col"><?php echo $cat['description'].' ('.$cat['percent'].'%)' ?></th>
        <?php endforeach; ?>
    </tr>
</thead>
<tbody class="text-center">
    <?php foreach($candidate_scores as $name => $scores): ?>
        <tr> 
            <td><?php echo $name ?></td>
            <?php foreach($categories as $cat): ?>
                <td>
                    <input type="number" name="score[]" class="form-control" 
                           value="<?php echo isset($scores[$cat['description']]) ? $scores[$cat['description']] : '' ?>">
                </td>
            <?php endforeach; ?>
        </tr>
    <?php endforeach; ?>
</tbody>

关键说明 #

• 原问题核心是数据结构不匹配：原始查询返回每条得分对应一行的扁平数据，而表格需要按候选人分组，每行展示所有类别得分。
• 通过PHP将原始数据整理为嵌套结构后，按固定类别顺序遍历渲染，即可保证得分对应到正确列中。
• 移除了不必要的distinct，后续通过分组整理即可实现候选人去重。

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