Laravel query builder group by error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Question

I'm building a forum. I have two models: Thread and Report.

A thread can be reported multiple times by different users.

I'm working on a page to display the threads that have been reported to moderators.

Now the problem is that I would group the reports by threads so that it shows how many times a single thread has been reported and the same thread doesn't show up multiple times if it has been reported multiple times.

Reports table:

$table->increments('id');
$table->unsignedInteger('thread_id');

Threads table:

$table->increments('id');
$table->string('title', 500);

Relationship Reports -> Threads

 public function thread() {
    return $this->hasOne(Thread::class, 'id');
 }

So I do this:

Report::with('thread')
        ->groupBy('thread_id')
        ->get();

However, I get this error:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'reports.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select * from `reports` group by `thread_id`)

Any idea how to fix this or approach it in another way? Thanks!

Group by clause in mySQL and postgreSQL, why the error in postgreSQL? With sql_mode=only_full_group_by you need to wrap every column with agg function. — Lukasz Szozda
– Lukasz Szozda, Commented Aug 30, 2018 at 15:34
It's hard to say without having some idea of your intentions. It doesn't make any sense to group by something without also selecting an aggregate (sum, count, etc...). Can you explain a little more about what you are trying to do/achieve exactly? — user1669496
– user1669496, Commented Aug 30, 2018 at 15:35

Amir Helmy · Accepted Answer · 2018-08-30 15:57:56Z

1

I would do it like this:

first: create a relationship in thread model like this:

public function reports() {
    return $this->hasMany(Report::class);
}

second:

in the controller will get all threads that have at least one report with reports count, like this:

$threads = Thread::has('reports')->withCount('reports')->get()

in the view you can print it out like this

@foreach($threads as $thread)
{{ $thread->id . ' has ' . $thread->reports_count . ' report'}}
@endforeach

answered Aug 30, 2018 at 15:57

Amir Helmy

1192 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hillcow Over a year ago

This is it. Thank you very much, fantastic solution.

Hillcow Over a year ago

One more question. I would like to sort the results by the date of the reports so that the threads that have been reported first show up first. Is this doable this way?

Amir Helmy Over a year ago

@Hillcow add new column in the threads table and name it something like this last_report_at and update it when new report is created, then use that column to order by it

Hussein · Accepted Answer · 2018-08-30 15:38:42Z

0

Maybe this works:

Report::with('thread')
    ->get()
    ->groupBy('thread_id');

answered Aug 30, 2018 at 15:38

Hussein

1,1431 gold badge9 silver badges16 bronze badges

Collectives™ on Stack Overflow

Laravel query builder group by error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related