mysql - Laravel 5.3: Syntax error or access violation: 1463 Non-grouping field 'distance' is used in HAVING clause

Question

This error came up after moving the whole source to the 5.3 version, and I'm scratching my head for over two hours now.

So I have this kind of eloquent query:

POI::select('*', DB::raw("SQRT( POW((x - {$this->x}),2) + POW((y - {$this->y}),2) ) AS distance"))
        ->where('status', Config::get('app.poi_state.enabled'))
        ->whereNotIn('id', $excludePOIList)
        ->having('distance', '<=', $distance)
        ->orderBy('distance')->get();

It worked find before upgrade now it throws:

Syntax error or access violation: 1463 Non-grouping field 'distance' is used in HAVING clause (SQL: select *, SQRT( POW((x - 860.0000),2) + POW((y - 105.0000),2) ) AS distance from poi where status = 1 and id not in (1) having distance <= 6 order by distance asc)

I wanted to check if the ONLY_FULL_GROUP_BY mode is enabled on my server, but it isn't...

SELECT @@sql_mode NO_ENGINE_SUBSTITUTION

The same query works fine in MySQL workbench. What's going on?

Answer 1

Check in the config/database.php file in the mysql conection that the strict is false:

'strict' => false,

If is true, put in false.