CODEWITHSUNDEEP
phpmysqllaravel-5query-builderlaravel-query-builder
CodeJam
CodeJam

Reputation: 119

Query relation and group by in query builder

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

Upvotes: 0

Views: 182

Answers (1)

Anmol Narang
Anmol Narang

Reputation: 551

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;

Upvotes: 1

Related Questions