Query builder GROUP BY, HAVING, COUNT in Laravel

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

MichalOravec

6 years ago

Best Answer

Level 75

$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.

UhOh

5 years ago

Level 7

I recommend reading up about SELECT + GROUP BY. It will save you huge amount of headaches to learn it now. Basically, whenever you use GROUP BY, all the select items have to be aggregates or expressions that handle aggregating the value. The exception to this is columns of unique values (like record ids). Learn it because you’ll encounter this all the time when GROUP BY is used.

Please or to participate in this conversation.