How to use DISTINCT ON in Laravel Eloquent

Question

Here is my full SQL Statement:

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;

I am trying to turn this statement into Laravel Eloquent code:

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,
    ]);
}

I have read that eloquent somehow does not support DISTINCT ON so I cannot just simply do distinctOn('group_id')
If this is the case, is there any way to modify the select inside the function to use DISTINCT ON?

that is why i am asking how to use it in eloquent or my select wont work as intended — , Jul 22 '21 at 16:59
You asked this question only two hours ago: [Laravel Controller: translating an SQL SELECT DISTINCT statement inside a function](https://stackoverflow.com/questions/68486871/laravel-controller-translating-an-sql-select-distinct-statement-inside-a-functi) — shaedrich, Jul 22 '21 at 17:10
this is different as I found out that Laravel Eloquent does something different with DISTINCT ON, I am just changing the nature of my question to better suit what is needed — , Jul 22 '21 at 17:21

score 1 · Accepted Answer · answered Jul 22 '21 at 19:18

Use DB::raw inside your select statement or add a selectRaw at the end. Here are some options:

->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
')

How to use DISTINCT ON in Laravel Eloquent

1 Answers1