Eager loading from questions table, with the corresponding answers

本文关键字：答案问题加载 | 更新日期: 2023-09-27

我试图弄清楚如何从我的questions表中获取"问题"，同时获取相应的answers，或者在我的特定情况下，我将它们称为选择。

目前我像这样获取它：

    public static function getQuestion($id)
{
    $sql = "SELECT * FROM questions WHERE id = :id";
    $query = $database->prepare($sql);
    $query->execute(array(':id' => $id));
    if($query->rowCount() == 1){
        return $query->fetch();
    }
        return false;
}
    public static function getChoices($id)
{
    $sql = "SELECT * FROM choices WHERE question_id = :id";
    $query = $database->prepare($sql);
    $query->execute(array(':id' => $id));
        return $query->fetchAll();
}

所以我正在做两（2）个查询，首先我获取问题，然后我获取问题选项。JSON 格式的结果是这样的：

{
  "question": {
    "id": "12",
    "content": "asdasd",
    "source": "asd",
    "image_url": "156ebc3206212c_qijpmnklohgfe.jpeg",
    "lastmodified": "2016-03-18 09:58:08",
    "quiz_id": "6"
  },
  "answerswers": [
    {
      "id": "45",
      "content": "Ja",
      "correct": "0",
      "question_id": "12"
    },
    {
      "id": "46",
      "content": "nej",
      "correct": "0",
      "question_id": "12"
    },
    {
      "id": "47",
      "content": "inte",
      "correct": "0",
      "question_id": "12"
    },
    {
      "id": "48",
      "content": "kanske ",
      "correct": "1",
      "question_id": "12"
    }
  ]
}

但他的"正确"方式可能是答案（选择）嵌套在问题中：

{
  "question": {
    "id": "12",
    "content": "asdasd",
    "source": "asd",
    "image_url": "156ebc3206212c_qijpmnklohgfe.jpeg",
    "lastmodified": "2016-03-18 09:58:08",
    "quiz_id": "6",
    "answerswers": [
      {
        "id": "45",
        "content": "Ja",
        "correct": "0",
        "question_id": "12"
      },
      {
        "id": "46",
        "content": "nej",
        "correct": "0",
        "question_id": "12"
      },
      {
        "id": "47",
        "content": "inte",
        "correct": "0",
        "question_id": "12"
      },
      {
        "id": "48",
        "content": "kanske ",
        "correct": "1",
        "question_id": "12"
      }
    ]
  }
}

我的问题：

我如何加入（或急切加载）问题的选择？

你可以编写第三个函数，使用连接拉取两者。

$sql = "SELECT 
q.id as question_id, q.content, q.source, q.image_url, q.lastmodified, q.quiz_id, 
c.id AS answer_id, c.content as an_content, c.correct, c.question_id 
FROM questions as q
INNER JOIN choices AS c ON q.id = c.question_id
WHERE q.id =  :id";

$database = DatabaseFactory::getFactory()->getConnection();
$question = Self::getFraga($id);
$answer = Self::getAnswers($id);
$question->answer = $answer;
return $question;

生产：

{
  "question": {
    "id": "12",
    "content": "asdasd",
    "source": "asd",
    "image_url": "156ebc3206212c_qijpmnklohgfe.jpeg",
    "lastmodified": "2016-03-18 09:58:08",
    "quiz_id": "6",
    "answerswer": [
      {
        "id": "45",
        "content": "Ja",
        "correct": "0",
        "question_id": "12"
      },
      {
        "id": "46",
        "content": "nej",
        "correct": "0",
        "question_id": "12"
      },
      {
        "id": "47",
        "content": "inte",
        "correct": "0",
        "question_id": "12"
      },
      {
        "id": "48",
        "content": "kanske ",
        "correct": "1",
        "question_id": "12"
      }
    ]
  }
}

毕竟非常简单的解决方案

从问题表中预先加载，并带有相应的答案

Eager loading from questions table, with the corresponding answers

我的问题：