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();

Murat Kaya · Accepted Answer · 2017-03-10 08:04:29Z

5

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

answered Mar 10, 2017 at 8:04

Murat Kaya

1,3114 gold badges28 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Anthony Over a year ago

It is a temporary solution, you probably should update your query.

DZDomi · Accepted Answer · 2017-03-09 21:53:27Z

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();

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related