How to execute Order by before Group by in cake php 3?

Question

I am fetching all answer given by a user. But I am only in the need of latest answer/response by the user(Using response id). I am running the bellow query.

$users_all_answers=$this->SurveySectionAnswers->find('all')
                             ->where(['survey_response_id IN'=>$response_ids])
                             ->order(['survey_response_id'=>'desc'])
                             ->group(['survey_question_id'])
                             ->hydrate(false)
                             ->toArray();

But I am getting the user answer, not by latest response because of execution on Group by before Order by. So is there any solution so that I can get all the answer by the latest responce of the user.

Getting array like this

[0] => Array
    (
        [id] => 527
        [survey_response_id] => 74
        [survey_question_id] => 84
        [survey_answer] => 
        [survey_score] => 0
        [survey_section_id] => 50
        [survey_answer_id] => 138
        [completed] => 1
    )

[1] => Array
    (
        [id] => 528
        [survey_response_id] => 74
        [survey_question_id] => 85
        [survey_answer] => 
        [survey_score] => 0
        [survey_section_id] => 48
        [survey_answer_id] => 142
        [completed] => 1
    )

But I want like

[0] => Array
    (
        [id] => 527
        [survey_response_id] => 76
        [survey_question_id] => 84
        [survey_answer] => 
        [survey_score] => 0
        [survey_section_id] => 50
        [survey_answer_id] => 138
        [completed] => 1
    )

[1] => Array
    (
        [id] => 528
        [survey_response_id] => 76
        [survey_question_id] => 85
        [survey_answer] => 
        [survey_score] => 0
        [survey_section_id] => 48
        [survey_answer_id] => 142
        [completed] => 1
    )

you can not do this in `mysql` , you may either use max/min functions and mix it with group by function , or use sub queries to get the latest answer then order it, or using php it-self to sort it after fetching your data; — hassan, Mar 22 '17 at 12:07

score 1 · Answer 1 · edited May 23 '17 at 10:29

In MySQL, GROUP BY is executed before ORDER BY. GROUP BY will always select the top row in the result set, so it's not affected by ORDER BY until that group is performed.

There's a number of techniques to do what you're describing in MySQL. I prefer to use the method where you join the table to itself in a way that ensures the latest row is the one that's actually selected.

In CakePHP that'd look something like:

$users_all_answers = $this->SurveySectionAnswers->find()
    ->join([
        'SurveySectionAnswers_2' => [
            'table' => 'survey_section_answers',
            'type' => 'LEFT',
            'conditions' => [
                'SurveySectionAnswers_2.survey_question_id' => new \Cake\Database\Expression\IdentifierExpression('SurveySectionAnswers_2.survey_question_id'),
                'SurveySectionAnswers.id <' => 'SurveySectionAnswers_2.id'
            ]
        ]
    ])
    ->where([
        'SurveySectionAnswers.survey_response_id IN' => $response_ids,
        'SurveySectionAnswers_2 IS ' => null
    ])
    ->hydrate(false)
    ->toArray();

This works because the LEFT JOIN lists all the possible combinations of the rows in order, but the WHERE clause filters all but the most recent (the one with no joined row) in the result set.

score 1 · Answer 2 · answered Jun 19 '18 at 15:00

Another alternative is to use Collections indexBy or groupBy.

This will have performance implications but could be clearer in code.

Get your list of answers. This will return an object with a collection interface.

$users_all_answers = $this->SurveySectionAnswers->find('all')
                         ->where(['survey_response_id IN'=>$response_ids])
                         ->order(['survey_response_id'=>'asc'])
                         ->hydrate(false);

It is very important to note that you must order your query in the reverse order as indexBy and groupBy will return the LAST item in the group

In the above query I changed order survey_response_id from desc to asc to accomplish this.

Then you can call groupBy or indexBy on your ordered query. This will immediately call your query.

$users_grouped_by_id = $users_all_answers->groupBy('survey_question_id')

Or if you only want 1 result per group

$users_indexed_by_id = $users_all_answers->indexBy('survey_question_id')

groupBy and indexBy are collection interface functions. Not to be confused with group which is a query builder function. Every query is a collection but collections are not queries.

How to execute Order by before Group by in cake php 3?

2 Answers2