Laravel SQLSTATE [42000]:语法错误或访问冲突:1055
问题描述:
我试图用laravel 5.3建立一个查询。但是当我做这个查询时,我得到这个错误。Laravel SQLSTATE [42000]:语法错误或访问冲突:1055
错误:
SQLSTATE[42000]: Syntax error or access violation: 1055 'laravel.location.locationDate' isn't in GROUP BY (SQL: select count(*), locationDate from
location
wheretagCode
= 24930 andxLocation
> -1 andxLocation
< 194 andyLocation
> 60 andyLocation
< 190 andcreated_at
> 2017-03-09 00:00:01 andcreated_at
< 2017-03-09 23:59:59 group by DATE(locationDate), hour(locationDate))
通过,如果我复制的查询,并尝试在SQL运行其工作的方式。但我只是加上引号,以created_at像“2017年3月9日00:00:01”
这是我的代码..
$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();
答
你必须使用在同一领域按这样的clausel:
$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();
我仍然getin同样的错误。 –