CODEWITHSUNDEEP
mysqllaraveleloquentquery-builderlaravel-6
Ikra
Ikra

Reputation: 173

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

Upvotes: 7

Views: 24645

Answers (3)

Basharmal
Basharmal

Reputation: 1384

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

Upvotes: 2

Ikra
Ikra

Reputation: 173

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();

Upvotes: 2

Ersoy
Ersoy

Reputation: 9594

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()

Upvotes: 14

Related Questions