Laravel - Use JOIN + COUNT

Question

I have two tables, one for users (users) and another for companies (Companies). I need to select the data of each company and know the amount of users per company.

This select in mysql works the way I need it:

SELECT companies.id as id, companies.name as name, companies.phone as phone, COUNT(*) as qtdUsers
FROM users
INNER JOIN companies 
ON users.company_id = companies.id 
GROUP BY company_id
ORDER BY qtdUsers DESC;

On the Laravel, he was as follows:

$teste = DB::table('users')
        ->join('companies', 'companies.id', '=', 'users.company_id')
        ->select('companies.id as id', 'companies.name as name', 'companies.phone as phone', DB::raw('count(*) as qtdUsers'))
        ->groupBy('users.company_id')
        ->orderBy('qtdUsers', 'desc')
        ->get();

But it is returning the following error:

lluminate\Database\QueryException
SQLSTATE[42000]: Syntax error or access violation: 1055 'proj01_db.companies.id' isn't in GROUP BY (SQL: select `companies`.`id` as `id`, `companies`.`name` as `name`, `companies`.`phone` as `phone`, count(*) as qtdUsers from `users` inner join `companies` on `companies`.`id` = `users`.`company_id` group by `users`.`company_id` order by `qtdUsers` desc)

When I put the 'Companies.id' in groupBy, the following error is returned:

Illuminate\Database\QueryException
SQLSTATE[42000]: Syntax error or access violation: 1055 'proj01_db.companies.name' isn't in GROUP BY (SQL: select `companies`.`id` as `id`, `companies`.`name` as `name`, `companies`.`phone` as `phone`, count(*) as qtdUsers from `users` inner join `companies` on `companies`.`id` = `users`.`company_id` group by `companies`.`id` order by `qtdUsers` desc)

Does anyone know how to solve it? Thanks in advance.

Answer 1

Find the file config/database.php look for the field 'strict' and set it to false.

'mysql' => [
    'strict' => false
]