2

How to use Laravel Eloquent with groupBy?

This is my code but I got errors all the time:

Message::whereColumn('host_id', 'sender_id')
                ->groupBy('host_id')
                ->orderBy('created_at', 'DESC')
                ->paginate(10);

The error message is:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'project_db.messages.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 messages where host_id = sender_id group by host_id order by created_at desc limit 10 offset 0)

What I'm trying to achieve is display messages as branches (groups), threads.

Dmitry Malys
  • 1,293
  • 4
  • 25
  • 46
  • try `->groupBy(['host_id', 'sender_id'])` – J. Doe Jul 20 '18 at 06:50
  • I don't have a clue about laravel, but for me a select * grouping by just one column looks weird (maybe the table has just that column, but it doesn't seem that...) – James Jul 20 '18 at 06:52
  • Column `sender_id` is a non aggregate function Either use it in group by clause or don't use strict mode by adding `strict => false` in `config/database.php` file – Saad Suri Jul 20 '18 at 06:54
  • try by dividing `whereColumn()` like `select` and `where` functions.Like `Message::select('host_id, sender_id'')->where('condition')` – Himanshu Upadhyay Jul 20 '18 at 06:55
  • This isn't a laravel specific issue. As of MySQL 5.7 the behaviour for `GROUP BY` changed. There is more info here: https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql – Rwd Jul 20 '18 at 07:00
  • If you give a little bit more information about what you're going to be doing with the data from this query then there is probably a workaround / alternative that can be provided :) – Rwd Jul 20 '18 at 07:02
  • You can disable `only_full_group_by` sql mode following [this answer](https://stackoverflow.com/questions/23921117/disable-only-full-group-by). – Tpojka Jul 20 '18 at 07:04
  • https://stackoverflow.com/questions/22322222/laravel-eloquent-orm-group-where – Ragupathi Jul 20 '18 at 07:05

0 Answers0