I’ve been searching for an example of this particular use case but have come up empty…

I have a model Onpage [onpages] which has many Report models [reports] through the pivot model OnpageReport [onpage_reports]…

On the Onpage model, my reports() method is as follows:

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

I’m trying to build a method that retrieves the last report generated for each calendar day (i.e. if on one day there are two reports, pick the latest one)… essentially grouping the Report model by date and limiting to 1 each date…

I tried the supplied solution from Github Copilot Chat, which was:

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

The above “solution” throws the following error when trying to view the relationship in 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)

Anyone know how I might accomplish taking this method all the way to the finish line?

~ Cheers and thanks in advance! ~