Laravel select count in select
I am using laravel 4.2, I have a table called pois that contains some poi (poi = point of interest), I have a second table called stamps that contain custom stamps.
So, I want to have 3 options for when the user has the maximum number of stamps. My problem is I don't know how to do this using laravel requests. I got my result using sql query, but this is not the best way to do it. here's my sql query:
$pois = DB::select("SELECT *, (SELECT count(*) from stamps WHERE pois.id = stamps.poi_id) nbr_stamps FROM pois order by nbr_stamps DESC limit 3");
Can you tell me how to do this with laravel requests?
source to share
You should use selectRaw()
instead select()
and separate other parts of the request from related methods:
$pois = DB::select(DB:raw("*, (SELECT count(*) from stamps WHERE pois.id = stamps.poi_id) nbr_stamps"))
->from('pois')
->orderBy('nbr_stamps', 'DESC')
->limit(3);
Read the Query Builder documentation .
source to share
@thanks to limonte for linking to the documentation, I found the correct way to write for choosing raw, here are the solutions:
$pois = DB::table('pois')
->select(DB::raw("*, (SELECT count(*) from stamps WHERE pois.id = stamps.poi_id) nbr_stamps"))
->orderBy('nbr_stamps', 'DESC')
->take(3)
->get();
To have a good day;)
source to share