How to apply distinct in payment_user_history.sid

Question

Here is my results in postman

and i want to show only one sid even if have 2 in results '''

"results": [ {
"sid": 2,
"id": 2,
"user_id": "S01202100002",
"name": "FRANK AMPLE WALKER",
"LRN": null,
"grade_level": 1
},
{
"sid": 2,
"id": 4,
"user_id": "S01202100002",
"name": "FRANK AMPLE WALKER",
"LRN": null,
"grade_level": 1
},
{
"sid": 3,
"id": 3,
"user_id": "S01202100003",
"name": "NEIL DEST MORGAN",
"LRN": null,
"grade_level": 2
}
]
}

here is my code

   public function billingpaymentaccount(){
    
    $fetch = DB::table('payment_user_history')
    ->leftJoin('tbl_accounts', 'payment_user_history.sid', '=', 'tbl_accounts.id')
    ->leftJoin('tbl_enrollment', 'payment_user_history.sid', '=', 'tbl_enrollment.student_id')
    ->select('payment_user_history.sid','payment_user_history.id', 'tbl_accounts.user_id', 
      DB::raw('concat(tbl_accounts.first_name, " ", tbl_accounts.middle_name, " ", 
      tbl_accounts.last_name) as name'), 'tbl_accounts.LRN', 'tbl_enrollment.grade_level')
    ->where('tbl_accounts.account_types',10)
    ->get();
    
    return response()->json(['results' => $fetch], 200);
}

Answer 1

You can use the ->distinct() modifier to return unique rows. However, payment_user_history.id needs to be removed from your select statement for the query to only return one row per sid.

// note the added distinct
$fetch = DB::table('payment_user_history')->distinct()
->leftJoin('tbl_accounts', 'payment_user_history.sid', '=', 'tbl_accounts.id')
->leftJoin('tbl_enrollment', 'payment_user_history.sid', '=', 'tbl_enrollment.student_id')
// note the removed payment_user_history.id
->select('payment_user_history.sid', 'tbl_accounts.user_id', DB::raw('concat(tbl_accounts.first_name, " ", tbl_accounts.middle_name, " ", tbl_accounts.last_name) as name'), 'tbl_accounts.LRN', 'tbl_enrollment.grade_level')
->where('tbl_accounts.account_types',10)
->get();

If you need to keep the payment_user_history.id field, then you have to decide how to aggregate it to one row (e.g., using the max or min and a group by clause).