Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Laravel eloquent get most common value in a database column

From a table animals I have the following values in the animal_name column

cat dog cat I want to pull the word cat from that because it is the most popular/common word in that column. How do I do that using laravel eloquent?

like image 446
user3714932 Avatar asked Jul 03 '17 21:07

user3714932


1 Answers

Eloquent:

App\Animal::select('name')
    ->groupBy('name')
    ->orderByRaw('COUNT(*) DESC')
    ->limit(1)
    ->get();

Output:

=> Illuminate\Database\Eloquent\Collection {#711
     all: [
       App\Animal {#725
         name: "cat",
       },
     ],
   }

Same thing with Query Builder:

DB::table('animals')
    ->select('name')
    ->groupBy('name')
    ->orderByRaw('COUNT(*) DESC')
    ->limit(1)
    ->get();

Output:

=> Illuminate\Support\Collection {#734
     all: [
       {#738
         +"name": "cat",
       },
     ],
   }

Any way to also fetch the "cat" count in the same query?

Sure there is

App\Animal::select('name')
    ->selectRaw('COUNT(*) AS count')
    ->groupBy('name')
    ->orderByDesc('count')
    ->limit(1)
    ->get();
=> Illuminate\Database\Eloquent\Collection {#711
     all: [
       App\Animal {#725
         name: "cat",
         count: 123
       },
     ],
   }
like image 88
peterm Avatar answered Nov 17 '22 18:11

peterm