Laravel SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2

Question

This might be obvious, and I may be completely missing the point, but I've got a table which contains multiple rows with user_id, site_id, e.g.

ldstaff | ld_site
____________________
   3         1
   3         2
   4         1 

A MySQL query originally written by an old dev got the staff id find their name and then concat all the sites together to make something like this.

StaffID | Name | sites
  3       Dave    1,2

This query was written initially as

SELECT `ld_staff` as staffID ,a.name,GROUP_CONCAT(`ld_site`) as sites FROM `lead_distribution` l

LEFT JOIN users a ON a.crm_id = l.ld_staff

WHERE ld_enabled = 1 AND

`account_type` = 1 AND `active` = 1 AND `no_more` = 0 AND network_team > 0 AND renewal_team = 0

GROUP BY `ld_staff`

However whenever we try to write it in Laravel as part of a new system to get the same results we have the error SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 no matter how we try to tamper with the query it always comes back to this. Reading up it seems sql_mode only_full_group_by doesn't allow this type of query, so I've changed sql_mode to = " " and this error persists.

Query

$data = DB::table('lead_distribution')
    ->join('users', 'ld_staff', '=', 'users.crm_id')
    ->select("lead_distribution.ld_staff", 'users.name', 
        DB::raw("GROUP_CONCAT(`ld_site`) as sites"))
    ->where('users.account_type', '=', 1)
    ->where('users.active', '=', 1)
    ->where('users.no_more', '=', 0)
    ->where('users.network_team', '>', 0)
    ->where('users.renewal_team', '=', 0)
    ->groupBy('lead_distribution.ld_staff')
    ->get();

Answer 1

@jaysingkar

Answer fixed it, my stupid mistake for missing it.

in config/database.php

Change 'strict' => true To 'strict' => false

Answer 2

config/database.php

Change 'strict' => true To 'strict' => false
and clear the cache
php artisan config:cache
then issue will be resolved.