CODEWITHSUNDEEP
mysqllaraveleloquentquery-builderlaravel-query-builder
Hillcow
Hillcow

Reputation: 969

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

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!

Upvotes: 0

Views: 619

Answers (2)

Amir Helmy
Amir Helmy

Reputation: 119

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

Upvotes: 1

Hussein
Hussein

Reputation: 1153

Maybe this works:

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

Upvotes: 0

Related Questions