Laravel Controller: translating an SQL SELECT DISTINCT statement inside a function

  controller, distinct, laravel, select, sql

Table List:

1

Here is the SQL as mentioned in the title:

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;  

test in pgAdmin: (only selecting distinct id)

2

I am trying to translate this inside a function in my Laravel Controller

Function:

public function fetchgroup(){
    $all_groups = HmsBbrGroup::join('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,
    ]);
}

The code above works differently though, the screenshot below is the output. The rows shown are different:

3

I tried changing up the orderBy but the rows shown are still not the correct id (the highest value id on every group_id)

any help would be appreciated thanks

UPDATE:

tried tweaking the function abit but the output is exactly the same as the screenshot:

    $all_groups = HmsBbrGroup::leftJoin('hms_bbr_group_type', 'hms_bbr_group.group_type_id', '=', 'hms_bbr_group_type.group_type_id')
                               ->distinct('hms_bbr_group.group_id')
                               ->orderBy('group_id', 'ASC', 'id', 'DESC')->get();

Source: Laravel

Leave a Reply