Marco Santos Marco Santos - 2 months ago 22
MySQL Question

eloquent query that finds top 5 records

Im creating a query thats gives me the top 5 countries with more visits, but my query isnt working.

My query i can limit , how would i count all record by country, to see the 5 countries more visited.

My query:

$top5 = DB::table('visits') ->select('ip','country', 'browser') ->groupBy('ip') ->get();


output example:
http://pastebin.com/wtu8CnL8

Answer

First you need to group your query by country, since that's what you're looking at. Then you need to count your results, order it by that number, and just take what you need.

 $top5 = DB::table('visits')
     ->select('ip','country', 'browser', DB::raw("count(*) as total_visits"))
     ->groupBy('country')
     ->orderBy('total_visits','DESC')
     ->take(5);
Comments