Laravel SQLSTATE [42000]: syntax error or access violation: 1055 expression No. 2

Question

This may be obvious, and I can completely miss the point, but I have a table that contains several rows with user_id, site_id, for example

ldstaff | ld_site
____________________
   3         1
   3         2
   4         1 

The MySQL query, originally written by an old developer, allowed the staff identifier to find its name and then combine all the sites together to do something similar.

StaffID | Name | sites
  3       Dave    1,2

This request was originally written 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'

, Laravel , , SQLSTATE [42000]: : 1055 № 2, , . , , sql_mode only_full_group_by , sql_mode = "", .

$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

, .

in config/database.php

'strict' => true 'strict' => false

Answer 2

/database.php

"strict" = > true To "strict" = > false

php artisan:
.

Answer 3

I ran into this problem and decided to solve it by including an aggregated function in the sentence GROUP BY.

I forgot where I read this, but the Aggregate functions should be included in the specified mode sql_mode only_full_group_by.

So try using ->groupBy(['lead_distribution.ld_staff','sites'])or->groupBy(['lead_distribution.ld_staff','users.name','sites'])

Convenient tip

Try to reset the SQL query created by laravel, replacing ->get()with ->toSql(), then try to run the query created by laravel and see if an error has occurred.