Framework: Eloquent left join group by count issue

[tombombadilll]

• Laravel Version: 5.5.20
• PHP Version: 7.0.15
• Database Driver & Version: MySQL 5.5.44

I have two tables, areas and object_areas. I want to get all areas and also add a count of the number of objects per area.

areas table have id, name, slug, content
object_areas table have id, area_id, name (area_id is null for a few rows)

Code to run:
$areas = Area::select('areas.id', DB::raw('count(area_id) as cnt')) ->leftJoin('object_areas', 'object_areas.area_id', '=', 'areas.id') ->groupBy('areas.id') ->get();

Error:
SQLSTATE[42000]: Syntax error or access violation: 1055 'projectname.areas.name' isn't in GROUP BY (SQL: select areas.*, count(object_areas.id) as cnt fromareasleft joinobject_areasonareas.id=object_areas.area_idgroup byareas.id)

If I copy this sql query in the error to HeidiSQL and run it, it works fine.

[haakym]

I don't think this is an issue with the framework, I think your sql code is invalid.

If you run:

Area::select('areas.id', DB::raw('count(area_id) as cnt'))->toSql()

You will get:

select `areas`.`id`, count(area_id) as cnt from `areas`

Yet, your error shows:

SQL: select areas.*, count(object_areas.id) as cnt from areas

The error you shared appears to be complaining that you didn't add areas.name into the group by because it's in your select statement, are you are only selecting areas.id instead of areas.*?

FYI, you can use three backtips for multi-line code blocks instead of one: https://guides.github.com/features/mastering-markdown/#syntax

[sisve]

Yes, the fault is at the sql code. Laravel has done A Good Thing(tm) and enabled strict mode, which means it tells mysql that it we actually care about the data and do not want mysql to mess it up for you. That's a summarization what strict mode is.

It works in heidisql because they do not care about your data as much as we do.

Read https://www.psce.com/en/blog/2012/05/15/mysql-mistakes-do-you-use-group-by-correctly/

[tombombadilll]

Thanks for the quick feedback. @haakym as you mentioned my code was suppose to be areas.* since I want all fields from the areas table and count rows in the other one.
That produces the error. When switching to areas.id it works fine.
I was testing different solutions and ended up posting the wrong code.

This is the code producing the error:

$areas = Area::select('areas.*', DB::raw('count(area_id) as connections'))
    ->leftJoin('object_areas', 'object_areas.area_id', '=', 'areas.id')
    ->groupBy('areas.id')
    ->get();

@sisve I checked the link you posted and read about it but I still don't understand.
So you are not allowed to "group by" on primary key in one table and count rows in another table and in the same sql get all data from primary key table? In the link the other value (12857) can change because of which come first, but in this case the group by is on the areas id (PK) so no values can change according to order. Is this also forbidden in STRICT mode?

I will switch to making two queries to fix this.

[sisve]

Yes, you happen to have a special case where we know that the query works based on the grouping on the primary keys. This is called "Functional Dependencies Derived from Keys" in https://dev.mysql.com/doc/refman/5.7/en/group-by-functional-dependence.html and something that was implemented in Mysql 5.7.5.

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

Source: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html Note that what they say about pre-5.7.5 is default values, which is overriden by using strict mode.