cakePHP3でサブクエリーを利用して最大値を取得する方法

Question

###前提・実現したいこと

以下のようなテーブルからexaminee_idとsurvey_idのペアでの最新のanswerの値を取得したいのですが、cakePHPとしてどのようにコードを記載したら良いのか分かりません。ご教授お願いします！

id	examinee_id	survey_id	answer	starttime
18	1	23	1	2017-06-30 13:55:57
19	1	18	2	2017-06-30 14:00:22
20	1	18	2	2017-06-30 14:07:20
21	1	18	1	2017-06-30 14:07:40
22	1	18	2	2017-06-30 16:25:20
23	1	19	1	2017-06-30 16:25:22
24	1	20	0	2017-06-30 16:25:26
25	1	30	2	2017-06-30 16:34:15
26	1	30	3	2017-06-30 18:00:59
27	1	31	0	2017-06-30 18:01:17
28	1	18	2	2017-07-01 18:29:48

###期待される取得結果

id	examinee_id	survey_id	answer	starttime
18	1	23	1	2017-06-30 13:55:57
23	1	19	1	2017-06-30 16:25:22
24	1	20	0	2017-06-30 16:25:26
26	1	30	3	2017-06-30 18:00:59
27	1	31	0	2017-06-30 18:01:17
28	1	18	2	2017-07-01 18:29:48

###SQL的には
以下のようにすると期待されるものが取得可能のようです。

SELECT *  
FROM survey_answers
WHERE survey_answers.id = (
    SELECT sas.id
    FROM survey_answers AS sas 
    WHERE survey_answers.examinee_id = sas.examinee_id
      and survey_answers.survey_id = sas.survey_id
    ORDER BY sas.starttime DESC
    LIMIT 1
)

もしくは、

SELECT *  
FROM survey_answers
WHERE NOT exists (
    SELECT 1
    FROM survey_answers AS sas 
    WHERE survey_answers.examinee_id = sas_b.examinee_id 
	  and survey_answers.survey_id = sas.survey_id
	  and survey_answers.id < sas.id
)

###補足情報(言語/FW/ツール等のバージョンなど)
PHP7.1/cakePHP3.4.8/mysql/

Answer 1

今回のご質問は「やってほしいことだけを記載した丸投げの質問」としてteratailで推奨されていないので、次回はご自身でも考えてみてくださいね。

参考：クエリ―ビルダー（サブクエリ）

SurveyAnswersControllerで取得する例です。

$sub = $this->SurveyAnswers->find()
	->select(['sas.id'])
	->from(['survey_answers AS `sas`'])
	->where(['SurveyAnswers.examinee_id = sas.examinee_id', 'SurveyAnswers.survey_id = sas.survey_id'])
	->order(['sas.starttime' => 'DESC'])
	->limit(1);
$query = $this->SurveyAnswers->find()
	->where(['SurveyAnswers.id' => $sub]);

debug($query->all());

CakePHP3で変換した後のSQL

SELECT 
  SurveyAnswers.id AS `SurveyAnswers__id`, 
  SurveyAnswers.examinee_id AS `SurveyAnswers__examinee_id`, 
  SurveyAnswers.survey_id AS `SurveyAnswers__survey_id`, 
  SurveyAnswers.answer AS `SurveyAnswers__answer`, 
  SurveyAnswers.starttime AS `SurveyAnswers__starttime` 
FROM 
  survey_answers SurveyAnswers 
WHERE 
  SurveyAnswers.id = (
    SELECT 
      sas.id AS `sas__id` 
    FROM 
      survey_answers AS `sas` 
    WHERE 
      (
        SurveyAnswers.examinee_id = sas.examinee_id 
        AND SurveyAnswers.survey_id = sas.survey_id
      ) 
    ORDER BY 
      sas.starttime DESC 
    LIMIT 
      1
  )

Comments

[takeshisan]

有難うございました。恐れ入りました。完璧です。無事に適用できました。