如何在Laravel Eloquent中使用DISTINCT ON

Question

这是我的完整SQL语句：

SELECT DISTINCT ON (hbg.group_id)
hbg.group_id,
hbg.id AS id,
hbg.group_name,
hbg.group_description,
hbg.group_type_id,
hbgt.group_type_name,
hbg.category_id,
hbg.effective_start_datetime,
hbg.created_by,
hbg.created_datetime,
hbg.archived_by,
hbg.archived_datetime
FROM hms_bbr_group hbg
LEFT JOIN hms_bbr_group_type hbgt
ON hbg.group_type_id = hbgt.group_type_id
ORDER BY
hbg.group_id,
hbg.id DESC;  

我正试图将此声明转换为Laravel Eloquent代码：

public function fetchgroup(){
$all_groups = HmsBbrGroup::leftJoin('hms_bbr_group_type', 'hms_bbr_group.group_type_id', '=', 'hms_bbr_group_type.group_type_id')
->distinct('group_id')
->orderBy('group_id', 'ASC', 'id', 'DESC')->get();
return response()->json([
'all_groups'=>$all_groups,
]);
}

• 我读到雄辩不知何故不支持DISTINCT ON，所以我不能简单地做distinctOn('group_id')
• 如果是这种情况，是否有任何方法可以修改函数内部的选择以使用DISTINCT ON

Answer 1

在select语句中使用DB::raw，或在末尾添加selectRaw。以下是一些选项：

->select(
DB::raw('distinct on (hbg.group_id)'),
'hbg.group_id',
...)

---

->select(...)
->selectRaw('distinct on (hbg.group_id)')

---

->selectRaw('DISTINCT ON (hbg.group_id)
hbg.group_id,
hbg.id AS id,
hbg.group_name,
hbg.group_description,
hbg.group_type_id,
hbgt.group_type_name,
hbg.category_id,
hbg.effective_start_datetime,
hbg.created_by,
hbg.created_datetime,
hbg.archived_by,
hbg.archived_datetime
')