php - 将另一个表中的项目数组连接到第一个表

Question

我有一个表定义为。

survey_question_answers

    id  |  surveyId | questionId | title | description | creationDate

survey_questions
        id  |  surveyId | title | description | creationDate

我正在使用mysql查询

SELECT `SurveyQuestion`.*, `Answer`.* FROM `uaehub`.`survey_questions` AS `SurveyQuestion` LEFT JOIN `uaehub`.`survey_question_answers` AS `Answer` ON (`SurveyQuestion`.`id` = `Answer`.`questionId`)  WHERE `SurveyQuestion`.`id` = 1  GROUP BY SurveyQuestion.id

问题是，它只获取 1 个答案，而不是所有答案。我如何修改查询以获取附加到我的调查问题的所有答案

我正在尝试在 cakephp 中执行此操作

$questions = $this->SurveyQuestion->find('all', array(   
                'conditions' => array('SurveyQuestion.id' => $survey['id']),
                'fields' => array(
                        'SurveyQuestion.*',
                        'Answer.*'
                ),
                'joins' => array(
                        array(
                                'type' => 'LEFT',
                                'table' => 'survey_question_answers',
                                'alias' => 'Answer',
                                'conditions' => array('SurveyQuestion.id = Answer.questionId')
                        ),                                                  
                ),
                'group' => array(
                        'SurveyQuestion'
                ),                
        ));

这就是我想要的

{
    "SurveyQuestion": {
      "id": "1",
      "surveyId": "1",
      "title": "Did you exercised early in the morning?",
      "description": "If Yes, please choose Yes. If no please choose NO",
      "creationDate": "2016-12-02 09:39:18"
    },
    "Answer": [{
      "id": "1",
      "surveyId": "1",
      "questionId": "1",
      "answer": "Yes",
      "creationDate": "2016-12-02 09:39:44"
    },
{
      "id": "2",
      "surveyId": "1",
      "questionId": "1",
      "answer": "NO",
      "creationDate": "2016-12-02 09:39:44"
    }]
  }

类似这样的事情，问题附有一系列答案

我在自动取款机上收到的是

{
    "SurveyQuestion": {
      "id": "1",
      "surveyId": "1",
      "title": "Did you exercised early in the morning?",
      "description": "If Yes, please choose Yes. If no please choose NO",
      "creationDate": "2016-12-02 09:39:18"
    },
    "Answer": {
      "id": "1",
      "surveyId": "1",
      "questionId": "1",
      "answer": "Yes",
      "creationDate": "2016-12-02 09:39:44"
    }
  }

注意，这是cakephp的格式

Answer 1

为什么使用 GROUP BY？这就是您只得到一行的原因。去掉 group by 就可以了。

GROUP BY 将对结果进行分组，以便(在您的情况下)对于任何 SurveyQuestion.id(= 每个问题)仅返回一行。