how to express this code in query builder. I'm using Laravel 6.
SELECT * FROM feedback GROUP BY noTicket having count(`status`) < 2
My Code:
$feedback = DB::table('feedback')
->groupBy('noTicket')
->having('count(status)', '<', 2)
->get();
Error Code:
SQLSTATE[42000]: Syntax error or access violation: 1055 'sifora.feedback.idFeedback' isn't in GROUP BY
(SQL: select * from `feedback` group by `noTicket` having `count(status)` < 2)
What is wrong with my code? It seems match between sql code vs query builder.
Thank you
This is the working version of query
select noTicket
from feedback
group by noTicket
having count(status) < 2;
This is the query builder;
return DB::table('feedback')
->groupBy('noTicket')
->having(DB::raw('count(status)'), '<', 2)
->pluck('noTicket'); // you may replace this with get()/select()
Here the complete code. Thanks a lot to Ersoy
$getArray = DB::table('feedback')
->groupBy('noTicket')
->having(DB::raw('count(status)'), '<', 2)
->pluck('noTicket');
$feedback = DB::table('feedback')
->whereIn('noTicket', $getArray)->get();
$feedback = DB::table('feedback')
->selectRaw('feedback.*, count(status) as count_status')
->groupBy('noTicket')
->havingRaw('count(status) > ?', [2])
->get();
Also there exists strict mode, you can disable it in config/database.php
'connections' => [
'mysql' => [
'strict' => false
]
]
But I don't recommend to you to do it. Check this https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html here you get more info how group by works.