mysql - Laravel 5.4 Native Query 在 MY SQL 服务器上正常运行，但无法通过 Laravel Eloquent Query Builder 正常运行。我做错了什么？

Question

我使用的是 Laravel 5.4。我用 Laravel eloquent 编写查询。当我手动将查询写入 MySQL 服务器时，它工作正常，但当我通过 Laravel Eloquent 编写相同的查询时，它无法工作。

请帮忙...

Laravel 代码

$dateS = new Carbon($req->fromDate);
$dateE = new Carbon($req->toDate);

$tasks = DB::table('taskday')->selectRaw("taskday.dayID, taskday.task_date, taskday.fk_userID, userdetails.fullname, count(assignedtask.fk_dayID) as total")
            ->join("assignedtask","assignedtask.fk_dayID","taskday.dayID")
            ->join("userdetails","userdetails.userID","taskday.fk_userID")
            ->whereBetween("taskday.task_date", [$dateS->format('Y-m-d')." 00:00:00", $dateE->format('Y-m-d')." 23:59:59"]);
if($req->staffname > 0){
    $tasks = $tasks->where('taskday.fk_userID',$req->staffname);
}

$tasks = $tasks->groupBy("assignedtask.fk_dayID")->get();

return $tasks;  

我的 SQL 查询

select `taskday`.`dayID`, `taskday`.`task_date`, `taskday`.`fk_userID`, `userdetails`.`fullname`, count(`assignedtask`.`fk_dayID`) as total 
from `taskday` inner join `assignedtask` on `assignedtask`.`fk_dayID` = `taskday`.`dayID` 
inner join `userdetails` on `userdetails`.`userID` = `taskday`.`fk_userID` 
where `taskday`.`task_date` between '2018-11-01 00:00:00' and '2018-12-19 23:59:59' 
group by `assignedtask`.`fk_dayID`

直接在 mysql 上进行查询后，它给出了正确的结果

dayID    task_date   fk_userID   fullname    total-------- ----------  ----------  ----------  -------------DAY021   2018-12-02  USR300920   Manish      2DAY022   2018-12-03  USR300924   Rovio       3

Laravel 出现错误

(2/2) QueryExceptionSQLSTATE[42000]: Syntax error or access violation: 1055 'medical.taskday.dayID' isn't in GROUP BY (SQL: select taskday.dayID, taskday.task_date, taskday.fk_userID, userdetails.fullname, count(assignedtask.fk_dayID) as total from `taskday` inner join `assignedtask` on `assignedtask`.`fk_dayID` = `taskday`.`dayID` inner join `userdetails` on `userdetails`.`userID` = `taskday`.`fk_userID` where `taskday`.`task_date` between 2018-11-01 00:00:00 and 2018-12-19 23:59:59 group by `assignedtask`.`fk_dayID`)

请帮忙

Answer 1

如果我理解你的问题，请尝试这个。

Controller

$dateS = new Carbon($req->fromDate);
$dateE = new Carbon($req->toDate);

$tasks = DB::table('taskday')
    ->where('taskday.task_date')
    ->selectRaw("taskday.dayID, taskday.task_date, taskday.fk_userID, userdetails.fullname, count(assignedtask.fk_dayID) as total")
    ->join('assignedtask.fk_dayID', 'assignedtask', '=', 'taskday.dayID')
    ->join('userdetails.userID', 'userdetails', '=', 'taskday.fk_userID')
    ->whereBetween('taskday.task_date', [date('Y-m-d 00:00:00', strtotime($dateS)), date('Y-m-d 23:59:59', strtotime($dateE))])
    ->groupBy('assignedtask.fk_dayID')
    ->get();

if($req->staffname > 0){
    $tasks = $tasks->where('taskday.fk_userID',$req->staffname);
}

$tasks = $tasks->groupBy("assignedtask.fk_dayID")->get();

return $tasks;  

请检查您在数据库中的时间戳格式，它必须与此相同或者尝试使用Unixtime格式查询“whereBetween”，以获得更准确的计算。

查看

. . .
   <tr>
     <th>dayID</th>
     <th>task_date</th>
     <th>fk_userID</th>
     <th>fullname</th>
     <th>total</t>
   </tr>
@foreach($tasks as $task)
   </tr>
     <td>$task->dayID</td>
     <td>$task->task_date</td>
     <td>$task->fk_userID</td>
     <td>$task->fullname</td>
     <td>$task->total</td>
   </tr>
@endforeach
. . .

为了编码的和平:)