0

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!

Hillcow
  • 890
  • 3
  • 19
  • 48
  • [Group by clause in mySQL and postgreSQL, why the error in postgreSQL?](https://stackoverflow.com/questions/33629168/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 Aug 30 '18 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 Aug 30 '18 at 15:35

2 Answers2

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

Amir Helmy
  • 121
  • 2
  • 3
  • This is it. Thank you very much, fantastic solution. – Hillcow Aug 30 '18 at 16:04
  • 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? – Hillcow Aug 30 '18 at 16:08
  • @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 – Amir Helmy Aug 30 '18 at 16:32
0

Maybe this works:

Report::with('thread')
    ->get()
    ->groupBy('thread_id');
Hussein
  • 1,143
  • 1
  • 9
  • 16