从问题表中急切加载，并附上相应的答案

Question

Im trying to figure out how i can fetch "questions" from my questions table, and at the same time fetch the corresponding answers, or in my specific case I have called them choices.

Currently I´m fetching it like this:

    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();
}

So I´m doing two (2) queries, first I'm fetching the question, then I'm fetching the question choices. The result in JSON format is this:

{
  "question": {
    "id": "12",
    "content": "asdasd",
    "source": "asd",
    "image_url": "156ebc3206212c_qijpmnklohgfe.jpeg",
    "lastmodified": "2016-03-18 09:58:08",
    "quiz_id": "6"
  },
  "answers": [
    {
      "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"
    }
  ]
}

But he "correct" way should probably be that the answers (choices) is nested inside the question:

{
  "question": {
    "id": "12",
    "content": "asdasd",
    "source": "asd",
    "image_url": "156ebc3206212c_qijpmnklohgfe.jpeg",
    "lastmodified": "2016-03-18 09:58:08",
    "quiz_id": "6",
    "answers": [
      {
        "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"
      }
    ]
  }
}

My question:

How do I join (or eager load) the choices to the question?

Answer 1

You could write a 3rd function that pulls both using a join.

$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";