英文:
MySQL grab last row of a group of data but specific to a column value
问题
我们正在使用Laravel开发一个系统，这意味着我们正在使用Laravel ORM。我们有一个类似于这样的表格：
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 |+----+-------------------------+--------+----------+----------+------+----------+
我们正在使用Laravel的morphTo()关系来获取关联表格数据：
public function app() {return $this->morphTo(__FUNCTION__, 'app_model', 'app_id');}
我尝试呈现一些数据，以便提供有关此表格中数据维度的一些上下文。我们在这个approvals表中保留了不同应用表的批准。一个机构可以根据每个申请获取修订批准多次。批准可以最初是'草稿'，后来变成'批准'。
👉 我们需要获取组（group_id）中状态为'批准'的最后一次修订行。
批准UI表格+-------+----------+--------+| GROUP | REVISION | ACTION |+-------+----------+--------+| 500 | - | [查看] || 501 | 1 | [查看] || 90 | 👁 2 | [查看] |+-------+----------+--------+
我们已经在Laravel ORM中制定了以下MySQL查询来实现这一目标：
// $userId = 获取当前用户的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');});// 仅显示属于该特定用户的批准。$query = $query->whereRelation('app', 'created_by', '=', $userId);$query = $query->orderBy('approvals.id', 'DESC');$query = $query->where('approvals.status', 'APPROVED'); // <-------- 🚩 此处存在问题return $query;
如果我们从查询中注释掉'APPROVED'条件，我们可以获取每个组的最后一行。问题是，组ID 90 的最后一行不是'批准'，但它会出现在表格中，这不是我们想要的。
👉 我们需要组的最后一行，但如果已批准，则应显示前一个最后一行。
在MySQL中，我们如何实现这一点呢？
英文:
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 HEREreturn $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?
答案1
得分: 1
要获取每个group_id的最新的APPROVED行。对于每个group_id，您要找到具有最大id且状态为APPROVED的行。
以下是我翻译好的代码部分：
$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');});// 只显示特定用户的批准。$query = $query->whereRelation('app', 'created_by', '=', $userId);$query = $query->orderBy('approvals.id', 'DESC');$query = $query->where('approvals.status', 'APPROVED');return $query;
希望这有助于您的需求。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论