Query works in phpMyAdmin, but not in Laravel

Question

If I write the following query in phpmyadmin, it runs and returns the correct result

select `u`.*, count(t.empid) as totalassignedtask from `users` as `u` left join `tasks` as `t` on `t`.`empid` = `u`.`id` where `u`.`role` = 'user' group by `t`.`empid`

However, if I wrote this query in laravel like this

$allemp = DB::table('users as u')
                        ->leftJoin('tasks as t','t.empid','=','u.id')
                        ->where('u.role','=','user')
                        ->select('u.*',DB::raw('count(t.empid) as totalassignedtask'))
                        ->groupBy('t.empid')
                        ->get();

I get an error:

Illuminate \ Database \ QueryException (42000)
SQLSTATE[42000]: Syntax error or access violation: 1055 'employee.u.id' isn't in GROUP BY (SQL: select u.*, count(t.empid) as totalassignedtask from tasks as t left join users as u on t.empid = u.id where u.role = user group by t.empid)

Answer 1

This is may be SQL_MODE problem.

In your config/database.php, in the connection, change mysql

strict => false

Answer 2

In config/database.php at "mysql" change :

'strict' => true,

to false. Hope it helps!

Answer 3

Thanks for answer - I changed the column name in code and it works

$allemp = DB::table('users as u')
                        ->join('tasks as t','t.empid','=','u.id')
                        ->where('u.role','=','user')
                        ->select('u.id','u.name','u.email',DB::raw('count(t.empid) as totalassignedtask'))
                        ->groupBy('u.id','u.name','u.email')
                        ->orderBy('totalassignedtask', 'asc')
                        ->get();