最近遇到一個需求是要更改排序的邏輯，原本想像應該很簡單，但實作起來卻出乎意料之外的麻煩！
原本的撈取邏輯：要取得某學員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;
}
}
