Laravel get latest row on joined table

Question

I have this query

$schedules = DB::table('project_schedule_reimbursments')
->where('project_schedule_reimbursments.project_id', $id)
->join('costs', 'costs.id', '=', 'project_schedule_reimbursments.cost_id')
->join('general_statuses', 'general_statuses.reimbrse_id', '=', 'project_schedule_reimbursments.id')

// ->join('statuses', function ($join) {
//     $join->on('general_statuses.status_id', '=', 'statuses.id');
// })

->join('statuses', 'statuses.id', '=', 'general_statuses.status_id')
->select(
    'project_schedule_reimbursments.id as id',
    'project_schedule_reimbursments.description as description',
    'costs.short_description as cost_name',
    'project_schedule_reimbursments.amount as amount',
    'project_schedule_reimbursments.spent_date as spent_date',
    'project_schedule_reimbursments.created_at as created_at',
    'statuses.name as statusName', // this supposed to return latest row
    DB::raw('max(general_statuses.created_at) as statusDate') // this is fixed now
)
->groupby('project_schedule_reimbursments.project_id')
->get();

and here is my database screenshot:

what I need here from this query is t get latest status detail which is row 8 from screenshot instead i'm getting details from row 1 which means first row instead of latest row.

The question is: where did I do mistake? how to fix this?

Update

here is the data i am receiving now

[
    {
        "id":17,
        "description":null,
        "cost_name":"Biaya Interview",
        "amount":null,
        "spent_date":"2019-12-18 00:00:00",
        "created_at":"2019-11-06 21:25:05",
        "statusName":"Rejected", // this is from row 1 (incorrect)
        "statusDate":"2019-12-20 09:14:46" // this is from row 8 (correct)
    }
]

Update 2

Logic

I need to get array of data cannot use limit(1)

1. I select project
2. It returns 20 results
3. Each result gets latest status name and time (each result has many status so i only get latest one)

Update 3

Thanks to Zar Ni Ko Ko it returns latest row however there is small issue.

I added another reimburse so when i select project i should have 2 results and each of them get their latest status (which seems to be fixed)

So now i have my reimburse of id 17 with latest status which comes from row 8 of this screenshot. But no reimburse of id 20 returns.

Answer 1

Try this ...

   $schedules = DB::table('project_schedule_reimbursments')
->where('project_schedule_reimbursments.project_id', $id)
->join('costs', 'costs.id', '=', 'project_schedule_reimbursments.cost_id')
->join('general_statuses', 'general_statuses.reimbrse_id', '=', 'project_schedule_reimbursments.id')

// ->join('statuses', function ($join) {
//     $join->on('general_statuses.status_id', '=', 'statuses.id');
// })

->join('statuses', 'statuses.id', '=', 'general_statuses.status_id')
->select(
    'project_schedule_reimbursments.id as id',
    'project_schedule_reimbursments.description as description',
    'costs.short_description as cost_name',
    'project_schedule_reimbursments.amount as amount',
    'project_schedule_reimbursments.spent_date as spent_date',
    'project_schedule_reimbursments.created_at as created_at',
    'statuses.name as statusName', // this supposed to return latest row
    DB::raw('max(general_statuses.created_at) as statusDate') // this is fixed now
)
->where('general_statuses.created_at', \DB::raw("(select max(`created_at`) from general_statues)"))
->groupby('project_schedule_reimbursments.project_id')
->get();