MySQL grab last row of a group of data but specific to a column value

We're developing a system using Laravel, which means we are using Laravel ORM. We have a table similar to this:

table: approvals
+----+-------------------------+--------+----------+----------+------+----------+
| id |        app_model        | app_id | group_id | revision | year |  status  |
+----+-------------------------+--------+----------+----------+------+----------+
|  1 | App\Models\AApplication |      4 |      500 | (NULL)   | 2021 | APPROVED |
|  2 | App\Models\AApplication |     95 |      501 | (NULL)   | 2022 | APPROVED |
|  3 | App\Models\BApplication |     22 |       90 | 1        | 2019 | APPROVED |
|  4 | App\Models\BApplication |     37 |       90 | 2        | 2020 | APPROVED |
|  5 | App\Models\AApplication |    125 |      501 | 1        | 2023 | APPROVED |
|  6 | App\Models\BApplication |     89 |       90 | 3        | 2024 | DRAFT    |
+----+-------------------------+--------+----------+----------+------+----------+

We're grabbing the relational table data using Laravel's morphTo() relation:

public function app() {
    return $this->morphTo(__FUNCTION__, 'app_model', 'app_id');
}

I tried to present some data so that I can provide some context about the dimensions of the data in this table. We're keeping approvals of different application tables in this approvals table. A single institute can get revised approval multiple times based on each of the application. The approval can be 'DRAFT' initially, later be 'APPROVED'.

👉 We need to grab the last revised row of the group (group_id) where the status is 'APPROVED'.

Approvals UI Grid
+-------+----------+--------+
| GROUP | REVISION | ACTION |
+-------+----------+--------+
|   500 |        - | [view] |
|   501 |        1 | [view] |
|    90 |     👉 2 | [view] |
+-------+----------+--------+

We've made the following MySQL query in Laravel ORM to achieve that:

// $userId = get current user id;

$query = DB::table('approvals')
    ->with(['app'])
    ->whereNotIn('id', function ($query) {
        $query->from('approvals as t1')
            ->crossJoin('approvals as t2')
            ->whereColumn('t1.id', '<', 't2.id')
            ->whereColumn('t1.group_id', '=', 't2.group_id')
            ->whereColumn('t1.app_model', '=', 't2.app_model')
            ->select('t1.id');
    });

// Only show approvals of _that_ particular user.
$query = $query->whereRelation('app', 'created_by', '=', $userId);

$query = $query->orderBy('approvals.id', 'DESC');

$query = $query->where('approvals.status', 'APPROVED'); // <-------- 🟥 ISSUE IS HERE

return $query;

If we comment out the 'APPROVED' condition from the query, we can get the last row of each group. Issue is that, the last row of group_id 90 is not 'APPROVED', but that comes into the grid which is not what we want.

👉 We need the last row of the group, but if approved, otherwise the previous-last row should come.

How can we achive this in MySQL?

You want to get the latest APPROVED row per group_id. For each group_id, you want to find the row with the maximum id where the status is APPROVED.

If that is the case, heres what i would do:

$query = DB::table('approvals')
    ->with(['app'])
    ->whereNotIn('id', function ($query) {
        $query->from('approvals as t1')
            ->join('approvals as t2', function ($join) {
                $join->on('t1.group_id', '=', 't2.group_id')
                     ->on('t1.app_model', '=', 't2.app_model')
                     ->on('t1.id', '<', 't2.id')
                     ->where('t2.status', 'APPROVED');
            })
            ->select('t1.id');
    });

// Only show approvals of _that_ particular user.
$query = $query->whereRelation('app', 'created_by', '=', $userId);

$query = $query->orderBy('approvals.id', 'DESC');

$query = $query->where('approvals.status', 'APPROVED');

return $query;

Edit: Explanation

The subquery in general returns all id where there's a row with the same group_id and app_model, as well as a greater id and finally the status of Approved. This essentially eliminates all but the latest APPROVED row for each group. The outer query then selects these latest APPROVED rows.