Laravel select count in select

Question

I use laravel 4.2, I'have a table called pois who contain some poi (poi = point of interest), i'have a second table called stamps who contain user stamps.

So i want to have the 3 pois where user have the maximum amount of stamp. My problem is i dont know how to this using laravel queries. I got my result using sql request, but it'snt the good way to do this. here my sql request :

$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 queries ?

Limon Monte · Accepted Answer · 2015-06-11 13:53:09Z

3

You should use selectRaw() instead of select() and separate other parts of query to 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.

edited Jun 11, 2015 at 13:53

answered Jun 11, 2015 at 13:01

Limon Monte

54.8k49 gold badges191 silver badges222 bronze badges

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

10 Comments

Deep Kakkar Over a year ago

Quick reply , Good one !

Jeffrey Over a year ago

thanks, but i got the following error : call_user_func_array() expects parameter 1 to be a valid callback, class 'Illuminate\Database\MySqlConnection' does not have a method 'selectRaw'

Limon Monte Over a year ago

@Barzull DB should be Illuminate\Support\Facades\DB. Add use DB to the top of the file and try again.

Jeffrey Over a year ago

i try with : use Illuminate\Support\Facades\DB as DB at the top of my controller; but nothing change ;(

Limon Monte Over a year ago

paste here the result of this: var_dump(get_class(new DB))

|

Jeffrey · Accepted Answer · 2015-06-11 14:17:02Z

@thanks to limonte for the documentation link, i found the right way to write to select raw, here 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();

have a nice day ;)

Collectives™ on Stack Overflow

Laravel select count in select

2 Answers 2

10 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Related