How to use where with groupBy eloquent on Laravel 5.8

Question

I'm working on a laravel project and I'am trying to use where with groupBy on my query. The below is example of drug_transactions table.

screen_id     patient_id    drug_id
    1             1            1
    1             1            2
    1             1            3
    2             1            1
    3             2            1

I would like to get screen_id for patient_id = 1. Then I use eloquent as below.

    $drugtransactiongroups = DrugTransaction::where('patient_id',1)
                                        ->groupBy('screen_id')
                                        ->get();

My need result should return screen_id= 1 and 2. But I have an error as below instead.

SQLSTATE[42000]: Syntax error or access violation: 1055 'zigmaclinic.drug_transactions.id' isn't in GROUP BY (SQL: select * from drug_transactions where patient_id = 1 group by screen_id)

Any advice or guidance on this would be greatly appreciated, Thanks.

Possible duplicate of Laravel groupBy Database query Builder not working — Vikash Pathak
– Vikash Pathak, Commented Jun 8, 2019 at 11:31

Hung Nguyen · Accepted Answer · 2019-06-08 11:28:49Z

2

You only can select fields that was used within groupBy

DrugTransaction::select('screen_id')
    ->where('patient_id',1)
    ->groupBy('screen_id')
    ->get();

answered Jun 8, 2019 at 11:28

Hung Nguyen

1,15611 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

joenpc npcsolution Over a year ago

Dear Hung Nguyen, Thank you very much.

Collectives™ on Stack Overflow

How to use where with groupBy eloquent on Laravel 5.8

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related