如何通过中间模型按日期分组获取每日最新Report数据？

我一直在寻找此类场景的示例但一无所获。我有一个Onpage模型（对应表onpages），它通过中间模型OnpageReport（对应表onpage_reports）关联多个Report模型（对应表reports）。Onpage模型中的关联方法如下：

public function reports(): BelongsToMany {
    return $this
        ->belongsToMany(Report::class, OnpageReport::class, 'onpage_id', 'report_id', 'id', 'id', 'reports')
        ->withPivot(['onpage_score','created_at']);
}

我需要实现一个方法，获取每个日历日生成的最后一条Report数据（同一天有多条时取最新的），也就是按日期对Report分组并每日仅保留一条。尝试了Copilot给出的方案：

public function dailyLatestReports(): BelongsToMany {
    return $this
        ->reports()
        ->selectRaw('DATE(reports.created_at) as date, count(*) as count')
        ->groupBy('date');
}

但在Laravel Nova中查看该关联时抛出错误：

Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'keddy.reports.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (Connection: mysql, SQL: select DATE(reports.created_at) as date, count(*) as count, `reports`.*, `onpage_reports`.`onpage_id` as `pivot_onpage_id`, `onpage_reports`.`report_id` as `pivot_report_id`, `onpage_reports`.`onpage_score` as `pivot_onpage_score`, `onpage_reports`.`created_at` as `pivot_created_at` from `reports` inner join `onpage_reports` on `reports`.`id` = `onpage_reports`.`report_id` where `onpage_reports`.`onpage_id` = 1 group by `date` order by `id` desc limit 6 offset 0) {"userId":1,"exception":"[object] (Illuminate\\Database\\QueryException(code: 42000): SQLSTATE: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'keddy.reports.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (Connection: mysql, SQL: select DATE(reports.created_at) as date, count(*) as count, `reports`.*, `onpage_reports`.`onpage_id` as `pivot_onpage_id`, `onpage_reports`.`report_id` as `pivot_report_id`, `onpage_reports`.`onpage_score` as `pivot_onpage_score`, `onpage_reports`.`created_at` as `pivot_created_at` from `reports` inner join `onpage_reports` on `reports`.`id` = `onpage_reports`.`report_id` where `onpage_reports`.`onpage_id` = 1 group by `date` order by `id` desc limit 6 offset 0)

Copilot的方案存在两个核心问题：

• 误用BelongsToMany返回关联：分组后的结果无法直接映射为标准多对多关联的模型集合，Laravel Nova加载关联时会自动追加reports.*和中间表字段到查询中，这些非聚合字段不在GROUP BY中，触发MySQLonly_full_group_by模式的SQL错误。
• 逻辑不符合需求：原方案用count(*)统计每日数量，而非筛选每日最新的单条记录。

方案1：子查询筛选每日最新记录（支持Nova关联展示） #

如果需要在Laravel Nova中作为关联展示，需通过子查询先找到每日最新的report_id，再关联查询完整数据：

use Illuminate\Database\Eloquent\Relations\BelongsToMany;

public function dailyLatestReports(): BelongsToMany
{
    // 子查询：获取每个日期对应的最新report_id
    $latestReportIds = Report::selectRaw('MAX(reports.id) as report_id')
        ->join('onpage_reports', 'reports.id', '=', 'onpage_reports.report_id')
        ->where('onpage_reports.onpage_id', $this->id)
        ->groupBy(DATE('reports.created_at'))
        ->pluck('report_id');

    return $this->belongsToMany(Report::class, 'onpage_reports', 'onpage_id', 'report_id')
        ->withPivot(['onpage_score', 'created_at'])
        ->whereIn('reports.id', $latestReportIds)
        ->orderBy('reports.created_at', 'desc');
}

注：假设id是自增主键，用MAX(id)确保取同一天中最新的记录；如果依赖created_at的时间戳，可替换为MAX(created_at)后匹配，但自增ID的方式更可靠。

方案2：Eloquent查询方法（返回数据集合，适合业务逻辑） #

如果不需要作为Nova关联，仅用于获取数据集合，可直接定义属性查询：

use Illuminate\Database\Query\Builder;

public function getDailyLatestReportsAttribute()
{
    return $this->reports()
        ->selectRaw('reports.*, onpage_reports.*')
        ->whereIn('reports.id', function (Builder $query) {
            $query->selectRaw('MAX(reports.id)')
                ->from('reports')
                ->join('onpage_reports', 'reports.id', '=', 'onpage_reports.report_id')
                ->where('onpage_reports.onpage_id', $this->id)
                ->groupBy(DATE('reports.created_at'));
        })
        ->orderBy('reports.created_at', 'desc')
        ->get();
}

补充说明 #

• MySQL的only_full_group_by模式要求SELECT中的非聚合字段必须出现在GROUP BY中，因此不能直接分组查询模型字段，必须先通过子筛选出符合条件的ID，再查询完整数据。
• 方案1的关联方法可直接在Nova资源的fields()中添加BelongsToMany::make('每日最新报告', 'dailyLatestReports', Report::class)来展示。

内容的提问来源于stack exchange，提问作者Chris at Keddy Digital