Query builder GROUP BY, HAVING, COUNT in Laravel

Question

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

Wildee

Unless you group by all columns, SELECT * and GROUP BY is not valid and make no sense. Also I doubt there is a column count(status). – Athirst

what are you trying to achieve by your query? – Hearsh

@party-ring: This mysql query already tested on Navicat and work perfectly. I want to group all same noTicket and choose noTicket which status < 2. – Wildee

@Wildee please share your table structure, sample data to reproduce it and expected result. Your query is incompatible because of group by. – Waylon

@Ersoy: My ticketing concept is, if someone created ticket and replied, it will store in feedback table. New ticket marked by status=1, assigned ticket marked by status=2. I want to display new ticket only which is status = 1 AND not assign yet. Because 1 noTicket can have multiple response and status will generate from 1 (waiting for response), 2 (In progress), 3 (completed), 4 (closed) Example noTicket = 4 --> My window was broken --> status = 1 noTicket = 4 --> Ok we will check and repair --> status = 2 so, noTicket = 4 can't show up in new ticket(s) – Wildee

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

Waylon

Almost 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 – Wildee

as 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 to select * – Intonation

@Wildee - also Hazem pointed out- i appended alternatives as comment to answer. It depends on how you want to display. – Waylon

@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 – Wildee

@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 used pluck('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.. – Wildee

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

Wildee

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

Carrier