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.

Improve this question
8
  • try ->groupBy(['host_id', 'sender_id']) Commented Jul 20, 2018 at 6: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...) Commented Jul 20, 2018 at 6: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 Commented Jul 20, 2018 at 6:54
  • try by dividing whereColumn() like select and where functions.Like Message::select('host_id, sender_id'')->where('condition') Commented Jul 20, 2018 at 6:55
  • This isn't a laravel specific issue. As of MySQL 5.7 the behaviour for GROUP BY changed. There is more info here: stackoverflow.com/questions/34115174/… Commented Jul 20, 2018 at 7:00
Load 4 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.