Laravel SQLSTATE[42000]: Syntax error or access violation: 1055

Question

I'm trying to build a query with laravel 5.3. But I'm getting this error when I make this query.

Error:

SQLSTATE[42000]: Syntax error or access violation: 1055 'laravel.location.locationDate' isn't in GROUP BY (SQL: select count(*), locationDate from location where tagCode = 24930 and xLocation > -1 and xLocation < 194 and yLocation > 60 and yLocation < 190 and created_at > 2017-03-09 00:00:01 and created_at < 2017-03-09 23:59:59 group by DATE(locationDate), hour(locationDate))

By the way if I copy query and try to run it in sql its working. but I'm just adding quotes to to created_at like '2017-03-09 00:00:01'

And this is my code..

$zoneTime = DB::table('location')
                            ->select(DB::raw('count(*), locationDate'))
                            ->where('tagCode', $tagCode)
                            ->where('xLocation', '>', $workingZone->x1)
                            ->where('xLocation', '<', $workingZone->x3)
                            ->where('yLocation', '>', $workingZone->y3)
                            ->where('yLocation', '<', $workingZone->y1)
                            ->where('created_at', '>', $startDate)
                            ->where('created_at', '<', $endDate)
                            ->groupBy(DB::raw('DATE(locationDate)'))
                            ->groupBy(DB::raw('hour(locationDate)'))
                            ->get();

Answer 1

I changed strict = false inside config/database and its worked.

Answer 2

You have to use the same field in the group by clausel like this:

$zoneTime = DB::table('location')
                        ->select(DB::raw('count(*), locationDate'))
                        ->where('tagCode', $tagCode)
                        ->where('xLocation', '>', $workingZone->x1)
                        ->where('xLocation', '<', $workingZone->x3)
                        ->where('yLocation', '>', $workingZone->y3)
                        ->where('yLocation', '<', $workingZone->y1)
                        ->where('created_at', '>', $startDate)
                        ->where('created_at', '<', $endDate)
                        ->groupBy(DB::raw('DATE(locationDate) as locationDate'))
                        ->groupBy(DB::raw('hour(locationDate)'))
                        ->get();