I had a similar problem a way back, same error but it was a difference between how mysql and mariadb handled the same query, where mysql was fine but mariadb required columns from the table to be put inside the group by.
But since you have the problem with the same sql instance, I'm not sure if it's the same problem.
If you add acd.call_ref to the group by as well, do you get a different error?
For example, I had a query that looked like
$payload['profile'] = Profiles::with([
'published' => function($query){
$query->with('files');
$query->withCount('files');
$query->with('data.data_array');
},
'files',
])->whereHas('current.services.callgroups', function($query) use ($payload){ // Fetch based on callgroups
$query->whereIn('callgroup_id', $payload['callgroups'] ?? []);
})
->whereHas('published')
->LeftJoin('viatel_profiles', function ($join) { // join viatel_profiles with profiles table based on id
$join->on('profiles.published_id', '=', 'viatel_profiles.version_id');
})
->groupBy('profiles.id', 'profiles.published_id', 'profiles.current_id', 'profiles.type_id', 'profiles.user_id', 'profiles.name', 'profiles.image_id', 'profiles.is_active', 'profiles.delete_requested', 'profiles.created_at', 'profiles.updated_at') // Group by profile_id to prevent duplicates
->select([
'profiles.*',
DB::raw('SUM(viatel_profiles.online_status) as viatel_profiles_online'),
DB::raw('COUNT(viatel_profiles.online_status) as viatel_profiles_total'),
DB::raw('(CASE WHEN COUNT(viatel_profiles.online_status) = SUM(viatel_profiles.online_status) THEN 1 ELSE 0 END) AS viatel_online_status') // Magic
])
->findOrFail($profile_id);
As you can see in my example, I had to add all the profiles columns inside the group by for it to give the same results as mysql only required the table.id.
Can't really say why it does it.
