Laravel query builder "where"

Question

i have the following query

select * from (SELECT accident.id,count(*) as cnt from accident left join driver on driver.accident_id = accident.id group by accident.id)alias where cnt = 1

and this is my query builder

$accidents = DB::table('accident')
        ->leftjoin('driver','accident.id','driver.accident_id')
        ->select(DB::raw('accident.*,count(*) as jumlah_kendaraan'))->groupBy('accident.id')
        ->where('jumlah_kendaraan', $jumlah_kendaraan);

i tried to convert it like the above but i got an error says

SQLSTATE[42703]: Undefined column: 7 ERROR: column "jumlah_kendaraan" does not exist

can anyone help me to solve it? thanks in advance

M Khalid Junaid · Accepted Answer · 2018-01-03 05:07:21Z

1

You cannot filter the result of an aggregate function count() using where clause, Instead use having for this purpose

->having('jumlah_kendaraan', $jumlah_kendaraan)

Or use your complete expression

->havingRaw('count(*) = '.$jumlah_kendaraan)

edited Jan 3, 2018 at 5:07

answered Jan 3, 2018 at 5:02

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

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

2 Comments

PinballWizard Over a year ago

i replace where with having but i still got the same error sir

M Khalid Junaid Over a year ago

@PinballWizard I have added another clause, Are you sure you got the same error or different error ?

Parag Soni · Accepted Answer · 2018-01-03 05:13:08Z

1

where clause can not use alias of group function directly, use

->where('count(*)', $jumlah_kendaraan);

You can use having as well but in case if having is not working use like given above.

answered Jan 3, 2018 at 5:13

Parag Soni

7137 silver badges14 bronze badges

Collectives™ on Stack Overflow

Laravel query builder "where"

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related