[SQL]查詢中如何同時orderBy A欄&groupBy B欄?

最近遇到一個需求是要更改排序的邏輯，原本想像應該很簡單，但實作起來卻出乎意料之外的麻煩！

原本的撈取邏輯：要取得某學員user的在特定區間的學習中的課程，而排序則依課程編號course_set_id由大至小排序。

需求：排序要改依照課程學習的時間來排序（由近至遠）

乍看之下，原以為只要調整orderby的欄位即可，但卻忽略了原本的groupby跟orderby都是course_set_id，因此如果此時要改成time這個欄位來排序，groupby 就需要多加上time 這個欄位才符合SQL規範。但加上後卻會對原本的資料結果產生巨大影響。

原query查詢如下：

/**
 * 學員進行中的課程
 * @param array $conditions
 * @param string $result_type
 * @param int $per
 * @return LengthAwarePaginator|int|null
 */
public function getCourseLearnLogByUser($conditions, $result_type = 'paginate', $per = 8)
{
    $result = CourseLearnLog::with(['course.courseSet'])
            ->whereHas('course.courseSet', function ($query) use ($conditions) {
                $query->available();
            })
            ->where('user_id', $conditions['user_id'])
            ->whereBetween('time', [$conditions['start_date'], $conditions['end_date']])
            ->groupBy('course_set_id')
            ->select(DB::raw('course_set_id as idSubChannel'));

    switch ($result_type) {
        case 'paginate':
            return $result
                ->orderby('course_set_id', 'desc')
                ->paginate($per);
        case 'count':
            return $result
                ->get()
                ->count();
        default:
            return null;
    }
}

Example：

原始資料：

course_set_id = 1, time = 2021-01-01 10:00:00

course_set_id = 1, time = 2021-01-01 12:22:33

course_set_id = 2, time = 2021-01-01 11:00:00

原本groupby.course_set_id最後的資料只會有兩筆：

course_set_id = 1 雖然有兩筆資料，但會被group起來，因此最後結果會是unique比數的course_set_id

但如果groupby.course_set_id, groupby.time兩個欄位的話，最後會有三筆

course_set_id + time兩個不一樣就會成一組，因此原始資料的3筆都會列入

解決方案

為了解決這個情況，因此想到了使用SubQuery，將同一堂課程的閱讀時間取最大值，之後再與MainQuery做join（以course_set_id 做關聯），並在MainQuery針對SubQuery的max_time做降冪排序，就可以免除用groupby的方式來處理！

調整後的query查詢如下：

/**
 * 學員進行中的課程
 * @param array $conditions
 * @param string $result_type
 * @param int $per
 * @return LengthAwarePaginator|int|null
 */
public function getCourseLearnLogByUser($conditions, $result_type = 'paginate', $per = 8)
{
    // SubQuery取得每個 course_set_id 的最後閱讀時間
    $timeSubQuery = CourseLearnLog::query()
        ->whereBetween('time', [$conditions['rule']['start_date'], $conditions['rule']['end_date']])
        ->where('user_id', $conditions['user_id'])
        ->groupBy('course_set_id')
        ->select('course_set_id', DB::raw('MAX(time) AS max_time'));

    $result = CourseLearnLog::with(['course.courseSet'])
            ->whereHas('course.courseSet', function ($query) use ($conditions) {
                $query->available();
            })
            ->joinSub($timeSubQuery, 'max_times', function ($join) {
                $join->on('course_learn_logs.course_set_id', '=', 'max_times.course_set_id');
            })
            ->whereBetween('time', [$conditions['start_date'], $conditions['end_date']])
            ->where('course_learn_logs.user_id', $conditions['user_id'])
            ->groupBy('course_learn_logs.course_set_id')
            ->select(DB::raw('course_learn_logs.course_set_id as idSubChannel'));

    switch ($result_type) {
        case 'paginate':
            return $result
                ->orderBy('max_times.max_time', 'desc')
                ->paginate($per);
        case 'count':
            return $result
                ->get()
                ->count();
        default:
            return null;
    }
}