Query builder GROUP BY, HAVING, COUNT in Laravel
10,004
Solution 1
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()
Solution 2
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();
Solution 3
$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.
Author by
Ikra
Updated on June 29, 2022Comments
-
Ikra almost 2 years
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
-
Ikra almost 4 yearsAlmost correct. The output are correct but not display all data. The output are in array. If use pluck, the output are [2,3] which is noTicket = 2 and noTicket = 3. If use get, the output are [noTicket = 2, noTicket = 3]. Thank you before
-
Hazem Mohamed almost 4 yearsas mentioned in the answer you can use
->get('noTicket')
or->select('noTicket')->get()
. If you don't do this the query builder will assume you want toselect *
-
Ersoy almost 4 years@Ikra - also Hazem pointed out- i appended alternatives as comment to answer. It depends on how you want to display.
-
Ikra almost 4 years@HazemMohamed: yes I already tried all alternative options as comment. I've got an error if I only use
->get
and->pluck
. On the other hand if I use->get('noTicket')
the result is in array ex: [2,3,5]. So I need to select where in array. Is there any easy way to get all data? thank you before -
Ikra almost 4 years@Ersoy: i've got error when I used
->get()
,->pluck()
,->select()
. There was no error if I used->get('noTicket
) or->pluck('noTicket')
but the result not show all data. If used->select('noTicket')
no error but get data as I'm not expected. So I usedpluck('noTicket')
to get value and then need to select where in array to retrieve all data. Is there any easy way to get all data? Thank you..