CodeIgniter 3中如何获取同一问题的所有关联答案数据

我用CodeIgniter 3开发了一款MCQ测验Web应用，需要从quiz_table、question_table和answers_table三个表获取数据并在视图展示。每个测验包含4个问题，每个问题对应4个答案（仅一个正确）。但目前编写的模型查询仅返回每个问题的第一个答案，附上模型代码及当前返回结果，希望修改查询以获取每个问题的全部4个答案。

原模型代码 #

function getSingleQuizQuestionDataFromDB($quizId)
{        //insert query
    try {
        $this->db->select('quiz_table.quizName');
        $this->db->select('quiz_table.creatorName');
        $this->db->select('quiz_table.rating');
        $this->db->select('question_table.questionId');
        $this->db->select('question_table.questionTitle');
        $this->db->select('question_table.correctAnswer');
        $this->db->select('answer_table.answer');
        $this->db->from('quiz_table');
        $this->db->where('quiz_table.quizId',$quizId);
        $this->db->join('question_table','question_table.quizId = quiz_table.quizId','INNER');
        $this->db->join('answer_table','answer_table.questionId= question_table.questionId');
        $this->db->group_by(['quiz_table.quizId', 'question_table.questionId']);
        $result = $this->db->get();


        $singleQuizQuestionData= $result->result_array();
        return $singleQuizQuestionData;
    } catch (Exception $e) {
        // log_message('error: ',$e->getMessage());
        return;
    }
}

当前返回结果 #

{
"singleQuizQuestionData": [
  {
     "quizName": "quiz 1",
     "creatorName": "kavibebi",
     "rating": "0",
     "questionId": "76",
     "questionTitle": "q1q1",
     "correctAnswer": "q1a",
     "answer": "q1q1a1"
  },
  {
     "quizName": "quiz 1",
     "creatorName": "kavibebi",
     "rating": "0",
     "questionId": "77",
     "questionTitle": "q1q2",
     "correctAnswer": "q1a",
     "answer": "q1q2a1"
  },
  {
     "quizName": "quiz 1",
     "creatorName": "kavibebi",
     "rating": "0",
     "questionId": "78",
     "questionTitle": "q1q3",
     "correctAnswer": "q1a",
     "answer": "q1q3a1"
  },
  {
     "quizName": "quiz 1",
     "creatorName": "kavibebi",
     "rating": "0",
     "questionId": "79",
     "questionTitle": "q1q4",
     "correctAnswer": "q1a",
     "answer": "q1q4a1"
  }
  ]
  }

问题出在你添加的group_by语句——按quizId和questionId分组后，每个问题只会返回第一条匹配的答案记录。要获取每个问题的全部4个答案，有两种可行的修改方式：

方式一：移除分组，在PHP中重组数据结构 #

先移除group_by语句，让查询返回所有问题-答案关联记录，再通过PHP循环把同一个问题的答案整合到一起：

function getSingleQuizQuestionDataFromDB($quizId)
{
    try {
        $this->db->select('quiz_table.quizName');
        $this->db->select('quiz_table.creatorName');
        $this->db->select('quiz_table.rating');
        $this->db->select('question_table.questionId');
        $this->db->select('question_table.questionTitle');
        $this->db->select('question_table.correctAnswer');
        $this->db->select('answer_table.answer');
        $this->db->from('quiz_table');
        $this->db->where('quiz_table.quizId', $quizId);
        $this->db->join('question_table', 'question_table.quizId = quiz_table.quizId', 'INNER');
        $this->db->join('answer_table', 'answer_table.questionId = question_table.questionId');
        // 移除group_by语句
        $result = $this->db->get();
        $rawData = $result->result_array();

        // 重组数据，将同一问题的答案合并到数组中
        $singleQuizQuestionData = [];
        foreach ($rawData as $row) {
            $questionId = $row['questionId'];
            if (!isset($singleQuizQuestionData[$questionId])) {
                // 首次遇到该问题，初始化基础数据
                $singleQuizQuestionData[$questionId] = [
                    'quizName' => $row['quizName'],
                    'creatorName' => $row['creatorName'],
                    'rating' => $row['rating'],
                    'questionId' => $row['questionId'],
                    'questionTitle' => $row['questionTitle'],
                    'correctAnswer' => $row['correctAnswer'],
                    'answers' => [] // 新增answers数组存储所有答案
                ];
            }
            // 添加当前答案到answers数组
            $singleQuizQuestionData[$questionId]['answers'][] = $row['answer'];
        }

        // 转换为索引数组（去掉questionId作为键）
        return array_values($singleQuizQuestionData);
    } catch (Exception $e) {
        // log_message('error: ', $e->getMessage());
        return [];
    }
}

修改后返回的结果结构会变成：

{
"singleQuizQuestionData": [
  {
     "quizName": "quiz 1",
     "creatorName": "kavibebi",
     "rating": "0",
     "questionId": "76",
     "questionTitle": "q1q1",
     "correctAnswer": "q1a",
     "answers": ["q1q1a1", "q1q1a2", "q1q1a3", "q1q1a4"]
  },
  ...
  ]
}

方式二：使用GROUP_CONCAT合并答案（适合直接在SQL层处理） #

如果不想在PHP中重组数据，可以用MySQL的GROUP_CONCAT函数把同一个问题的所有答案合并成一个字符串，之后在视图或控制器中拆分：

function getSingleQuizQuestionDataFromDB($quizId)
{
    try {
        $this->db->select('quiz_table.quizName');
        $this->db->select('quiz_table.creatorName');
        $this->db->select('quiz_table.rating');
        $this->db->select('question_table.questionId');
        $this->db->select('question_table.questionTitle');
        $this->db->select('question_table.correctAnswer');
        // 使用GROUP_CONCAT合并所有答案，用逗号分隔
        $this->db->select('GROUP_CONCAT(answer_table.answer SEPARATOR ",") AS answers');
        $this->db->from('quiz_table');
        $this->db->where('quiz_table.quizId', $quizId);
        $this->db->join('question_table', 'question_table.quizId = quiz_table.quizId', 'INNER');
        $this->db->join('answer_table', 'answer_table.questionId = question_table.questionId');
        $this->db->group_by(['quiz_table.quizId', 'question_table.questionId']);
        $result = $this->db->get();

        $singleQuizQuestionData = $result->result_array();
        // 可选：将answers字符串拆分为数组
        foreach ($singleQuizQuestionData as &$item) {
            $item['answers'] = explode(',', $item['answers']);
        }
        unset($item); // 释放引用

        return $singleQuizQuestionData;
    } catch (Exception $e) {
        // log_message('error: ', $e->getMessage());
        return [];
    }
}

这种方式返回的结果结构和方式一一致，区别在于合并拆分的时机在SQL层还是PHP层。

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