CODEWITHSUNDEEP
phppostgresqllaravel-5
PinballWizard
PinballWizard

Reputation: 141

Laravel query builder "where"

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

Upvotes: 1

Views: 70

Answers (2)

Parag Soni
Parag Soni

Reputation: 713

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.

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

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)

Upvotes: 1

Related Questions