How to fix column is not in a groupBy laravel?-CodePudding

I am facing this error for the past 2-3 days. I hold this task to check this later in my free time. But still, I am getting the same error. Please help me to find the error. I am tired to check all solutions on google. But nothing works.

Error

Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access violation: 1055 'dating-app.messages.id' isn't in GROUP BY (SQL: select * from messages group by room_id having is_read = 0) in file C:\xampp\htdocs\dating-app\vendor\laravel\framework\src\Illuminate\Database\Connection.php on line 692

Here is my query

$messages = Message::groupBy('room_id')->having('is_read', 0)->get();
return response()->json(['status' => true, 'message' => $messages]);`

Any solution appreciated!

CodePudding user response：

That's because any column you use in a select statement must appear in the group by clause. So select * and group by don't get along. Remove groupBy('room_id') in your relationship and see how that goes.

Alternatively, disable MySQL strict mode by going into config/database.php and setting strict => false for MySQL, if you are using MySQL version >5.7.

source

CodePudding user response：

Try to this

$users = DB::table('table_name')
                ->groupBy('room_id')
                ->having('is_read', '=', 0)
                ->get();
return response()->json(['status' => true, 'message' => $messages]);`