如何用groupBy和orderBy获取两表关联的最新插入数据

你的问题出在groupBy的使用逻辑上：当你按remcal.invoice_id分组后，虽然通过MAX(remcal.id)拿到了每组最大的记录ID，但select里的remcal.*、inv.*并不会自动关联到这个最大ID对应的那条记录——数据库会从同组的记录里随机选取一条返回，所以自然拿不到最新插入的数据。

下面提供两种可行的解决方案：

方案一：子查询预取最新记录ID #

先通过子查询找出每个invoice_id对应的最新reminders_calendar记录ID，再关联原表获取完整数据：

$def_reminders = Invoice::from('invoices AS inv')
    ->join(
        DB::raw('(SELECT invoice_id, MAX(id) AS max_remcal_id FROM reminders_calendar WHERE date_when = "2024-01-03" AND reminder_type IN ("InProgress", "Waiting") GROUP BY invoice_id) AS latest_rem'),
        'latest_rem.invoice_id', '=', 'inv.id'
    )
    ->join('reminders_calendar AS remcal', 'remcal.id', '=', 'latest_rem.max_remcal_id')
    ->where([
        'inv.user_id' => 1,
        'inv.is_deleted' => '0',
    ])
    ->where('inv.reminder_status', '!=', 'Enabled')
    ->select('remcal.*', 'inv.*')
    ->orderBy('remcal.id', 'desc')
    ->get();

方案二：窗口函数（MySQL 8.0+适用） #

利用窗口函数ROW_NUMBER()给每个invoice_id的记录按ID倒序排号，直接取每组的第一条最新记录：

$def_reminders = Invoice::from('invoices AS inv')
    ->join(
        DB::raw('(SELECT *, ROW_NUMBER() OVER (PARTITION BY invoice_id ORDER BY id DESC) AS rn FROM reminders_calendar WHERE date_when = "2024-01-03" AND reminder_type IN ("InProgress", "Waiting")) AS remcal'),
        'remcal.invoice_id', '=', 'inv.id'
    )
    ->where([
        'inv.user_id' => 1,
        'inv.is_deleted' => '0',
        'remcal.rn' => 1
    ])
    ->where('inv.reminder_status', '!=', 'Enabled')
    ->select('remcal.*', 'inv.*')
    ->orderBy('remcal.id', 'desc')
    ->get();

说明 #

• 方案一兼容所有MySQL版本，适合老项目；
• 方案二代码更简洁，逻辑清晰，但要求MySQL版本在8.0及以上。

内容的提问来源于stack exchange，提问作者Mano Mahe