Groupe Byを使用して、一つのフィールドが重複しないように、且つあるもう一つのフィールドが最新のレコードを取得する

現在、Laravelのquery文は下記になっている。

/**
     * スコープを設定
     *
     * @link https://readouble.com/laravel/8.x/ja/eloquent.html スコープについて
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @param  mixed  $request
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeNegotiationList($query, $request)
    {
        // 検索項目設定
        $query->select(
            'negotiations.negotiation_id',
            'proposed_products.proposed_product_id',
            'actives.user_id',
            'negotiations.type',
        );

        // LEFT JOIN 設定
        $query->leftjoin('actives', 'actives.negotiation_id', '=', 'negotiations.negotiation_id');
        $query->leftjoin('users', 'actives.user_id', '=', 'users.user_id');
        $query->leftjoin('proposed_products', 'proposed_products.negotiation_id', '=', 'negotiations.negotiation_id');

        $query->where('negotiations.branch_office_id', $request->branch_office_id);


        // 並び順設定
        // 「活動.日付」降順
        $query->orderBy('actives.active_date', 'desc');

        return $query;
    }

上記の場合だと、sql文は下記のようになっている。

select `negotiations`.`negotiation_id`,  `proposed_products`.`proposed_product_id`, `actives`.`user_id`, `negotiations`.`type` from `negotiations` left join `actives` on `actives`.`negotiation_id` = `negotiations`.`negotiation_id` left join `users` on `actives`.`user_id` = `users`.`user_id` left join `proposed_products` on `proposed_products`.`negotiation_id` = `negotiations`.`negotiation_id` where `negotiations`.`branch_office_id` = 1 and `negotiations`.`deleted_at` is null order by `actives`.`active_date` desc

出力結果はこんな感じになっている。

negotiation_id	proposed_product_id	user_id
1	1	3
1	2	6
2	3	6
2	4	3
3	5	6
3	6	6

やりたい事としては、negotiation_idのフィールドを重複せず取得し、且つproposed_product_idは
negotiation_idと紐づくレコードの最大値(最新の値)を取得したい。

修正後のスコープ文。

     /*
     * 
     *
     * @link https://readouble.com/laravel/8.x/ja/eloquent.html スコープについて
     * @param  \Illuminate\Database\Eloquent\Builder  $query
     * @param  mixed  $request
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function scopeNegotiationList($query, $request)
    {
        // 検索項目設定
        $query->select(
            'negotiations.negotiation_id',
            DB::raw('MAX(proposed_products.proposed_product_id) As proposed_product_id'),
            'actives.user_id',
        );

        // LEFT JOIN 設定
        $query->leftjoin('actives', 'actives.negotiation_id', '=', 'negotiations.negotiation_id');
        $query->leftjoin('users', 'actives.user_id', '=', 'users.user_id');
        $query->leftjoin('proposed_products', 'proposed_products.negotiation_id', '=', 'negotiations.negotiation_id');

        $query->where('negotiations.branch_office_id', $request->branch_office_id)->groupBy('negotiation_id');


        // 並び順設定
        // 「活動.日付」降順
        $query->orderBy('actives.active_date', 'desc');

        return $query;
    }

修正後のsql文

select `negotiations`.`negotiation_id`, MAX(proposed_products.proposed_product_id) As proposed_product_id, `actives`.`user_id`, from `negotiations` left join `actives` on `actives`.`negotiation_id` = `negotiations`.`negotiation_id` left join `users` on `actives`.`user_id` = `users`.`user_id` left join `proposed_products` on `proposed_products`.`negotiation_id` = `negotiations`.`negotiation_id` where `negotiations`.`branch_office_id` = 1 and `negotiations`.`deleted_at` is null group by `negotiation_id` order by `actives`.`active_date` desc

修正後の出力結果

negotiation_id	proposed_product_id	user_id
1	2	6
2	4	3
3	6	6

スコープ内のqueryを下記部分を追加すると、やりたい事が出来るようになりました。

DB::raw('MAX(proposed_products.proposed_product_id) As proposed_product_id'),
$query->where('negotiations.branch_office_id', $request->branch_office_id)->groupBy('negotiation_id');

gropByで、negotiation_idごとにグループ化をして、各グループの中からMAXを使用して、proposed_product_idの最大値を取得し、最後にnegotiation_idで重複しているレコードを削除しているイメージですかね。

以上となります。