Query relation and group by in query builder

Question

Getting a sql error in my query, i believe maybe is malformed my query builder but cant figure out what im doing wrong.

Alredy tried testing my query directlly in the DB and it works, but when translating with query builder it doesnt work.

$totalUserForCountry = DB::table('countries')
    ->select('countries.name', DB::raw('COUNT(users.country_id) as total'))
    ->join('users', 'users.country_id', '=', 'countries.id')
    ->groupBy('users.country_id')
    ->get();

My error code is:

SQLSTATE[42000]: Syntax error or access violation: 1055 'db.countries.name' isn't in GROUP BY (SQL: select countries.name, COUNT(users.country_id) as total from countries
inner join users on users.country_id = countries.id group by users.country_id)

Mysql query directlly to DB:

SELECT ct.name AS CountryName, COUNT(us.country_id) AS NumberOfUsers FROM countries AS ct
        INNER JOIN users AS us
        ON
        us.country_id = ct.id
        GROUP BY us.country_id;

P.S. It only works if i disable 'strict' mode to false

This is not how MySQL handles GROUP BY .. The generated SQL is SELECT countries.name, COUNT(users.country_id) as total FROM countries INNER JOIN ... users ON ,,, GROUP BY users.country_id which is invalid ANSI/iSO SQL 92 — Raymond Nijland
As your query is wrong i would suggest to read Why should I provide a Minimal Reproducible Example for a very simple SQL query? and provide example data and expected results.. i know how i would need to write it in SQL as most do here but iam not 100% sure off the most easy method how to make it work in Laravel most likely you would need to use Sub-Query Joins — Raymond Nijland
Alredy [sic] tried testing my query directlly [sic] in the DB and it works - Prove it. — Strawberry
Cound't you just add the asked field ? ->groupBy('users.country_id', 'countries.name') — Frankich

Anmol Narang Anmol Narang · Accepted Answer · 2019-05-28T12:07:21

when using group by clause in sql query, the select clause can have only those columns which you have used in group clause.

the sql query for the above problem will be:

SELECT ct.name AS CountryName, COUNT(us.country_id) AS NumberOfUsers FROM countries AS ct
    INNER JOIN users AS us
    ON
    us.country_id = ct.id
    GROUP BY us.country_id,ct.name;

Query relation and group by in query builder

1 Answers