Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

cjholowatyj's avatar

Eloquently GroupBy Date on Related Model with Pivot?

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! ~

0 likes
10 replies
aleahy's avatar

The key is this part of the error statement: sql_mode=only_full_group_by. This means that all the fields that you have in the select part of your query needs to be in the group by and you've got quite a few things:

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`    <-- Add the other fields in the select here
order by `id` desc 
limit 6 offset 0;
cjholowatyj's avatar

@aleahy I’m already aware of that. How do I arrive at a solution to the original question posed?

aleahy's avatar

@cjholowatyj Add the fields to groupBy('date') in your dailyLatestReports method.

Eg:

public function dailyLatestReports(): BelongsToMany {
    return $this
	    ->reports()
        ->selectRaw('DATE(reports.created_at) as date, count(*) as count')
        ->groupBy(['date', 'pivot_onpage_id', 'pivot_report_id', etc]);
}
cjholowatyj's avatar

@aleahy The third expression in the statement is reports.*, which is the part of the statement causing problems. Laravel Nova adds this to the query... How do I add reports.* to the group by query?

cjholowatyj's avatar

@aleahy Also, AFAIK If I add more columns to the group by statement, then it is grouping by each unique pairing of each of the columns in the group by statement, which is not the objective of this project

aleahy's avatar

@cjholowatyj Regarding adding reports.* to the group by query, adding the primary key of the table will do it. But if this works against what you want to achieve, then I don't think this structure the way to do it. I think your query needs to be more complicated than that.

Something I've referred to a couple of times in getting most recent records is this one: https://thoughtbot.com/blog/ordering-within-a-sql-group-by-clause

The formatting seems to be all gone, but the content is really helpful. It's working with raw sql, but if you can work out your query in raw sql, there are eloquent methods to achieve the same thing.

I suspect what you want to achieve will require a sub query and a join, which might be easier with a direct eloquent query rather than defining a relationship.

cjholowatyj's avatar

@aleahy My biggest challenge right now is the fact that I think Laravel Nova is trying to combine mysql queries when I try and show this query as a relationship in my Laravel Nova app. Not sure how I'm going to work around that (I might just have to resign myself to not ever seeing this particular query in Laravel Nova).

I've managed to piece together a working SQL query in DataGrip that does what I want it to...

SELECT onpage_reports.id, onpage_reports.onpage_score, onpage_reports.created_at
FROM
    (SELECT max(id) as max_id, DATE(r.created_at) AS date, max(r.created_at) as max_created_at
    FROM onpage_reports AS r
    WHERE r.onpage_id = 1
    GROUP BY date ORDER BY max_created_at DESC) as available_dates
INNER JOIN
    onpage_reports
ON
    onpage_reports.onpage_id = 1 AND
    available_dates.max_id = onpage_reports.id

Now I'm just trying to figure out if there is an eloquent way of creating this query in laravel, or if I just have to do raw sql...

aleahy's avatar

With Eloquent, it might look something like this:

DB::table(function($query) use ($id) {
    $query->select([
        DB::raw('max(id) as max_id'), 
        DB::raw('DATE(r.created_at as date'), 
        DB::raw('max(r.created_at) as max_created_at')
    ])
    ->from('onpage_reports as r')
    ->where('r.onpage_id', $id)
    ->groupBy('date')
    ->orderBy('max_created_at', 'desc');
}, 'available_dates')
->join('onpage_reports as r2', function($join) {
    $join->on('r2.onpage_id', '=', $id)
        ->on('available_dates.max_id', '=', 'r2.id');
})->get();
cjholowatyj's avatar

@aleahy Just tested things out, and this is the resulting query that ended up working...

DB::query()
    ->selectRaw('r1.id, r1.onpage_score, r1.created_at')
    ->fromRaw('onpage_reports as r1')
    ->joinSub(function ($query) use ($id) {
        $query->selectRaw('max(r2.id) as max_id, DATE(r2.created_at) AS date, max(r2.created_at) as max_created_at')
            ->fromRaw('onpage_reports as r2')
            ->whereRaw('r2.onpage_id = ?', [$id])
            ->groupByRaw('date')
            ->orderByRaw('max_created_at DESC', );
    }, 'available_dates', function ($join) use ($id) {
        $join->on('r1.onpage_id', '=', DB::raw($id))
            ->on('available_dates.max_id', '=', 'r1.id');
    })->get();
aleahy's avatar

@cjholowatyj I would get rid of some of those Raw statements as

  1. They're not needed, and
  2. If the id arrives as something other than an actual id (i.e. sql injection), you're in trouble.

The only time you need raw here is for the DB functions in the select.

DB::query()
    ->select(['r1.id, r1.onpage_score, r1.created_at'])
    ->from('onpage_reports as r1')
    ->joinSub(function ($query) use ($id) {
        $query->selectRaw('max(r2.id) as max_id, DATE(r2.created_at) AS date, max(r2.created_at) as max_created_at')
            ->from('onpage_reports as r2')
            ->where('r2.onpage_id', $id)
            ->groupBy('date')
            ->orderBy('max_created_at',  'DESC');
    }, 'available_dates', function ($join) use ($id) {
        $join->on('r1.onpage_id', '=', $id)
            ->on('available_dates.max_id', '=', 'r1.id');
    })->get();

Please or to participate in this conversation.