I am trying to generate a report in Laravel that counts the number of users per city. In raw SQL, I would simply run SELECT city, COUNT(*) as total FROM users GROUP BY city. However, when I try to translate this into Eloquent, I’m unsure how to include the count aggregate alongside the group by clause without resorting to a completely raw query. Should I be using DB::raw within a select() method, or is there a more "Eloquent-way" to handle grouping and counting simultaneously?
3 answers
To achieve this in Eloquent, the most common and efficient approach is to use the selectRaw() method. This allows you to include the SQL aggregate function while still returning a collection of Eloquent models (or generic objects).
The syntax would look like this: $results = User::selectRaw('city, count(*) as total')->groupBy('city')->get();
This generates the exact SQL you’re looking for. Once executed, you can access the count just like any other property on your model: $results->first()->total. It’s important to remember that if your config/database.php has strict mode set to true (which is the default), you must include every column mentioned in your select inside the groupBy clause to avoid SQL syntax errors.
That works perfectly for getting the data, but what if I want to sort the results so that the cities with the highest number of users appear first? Do I just chain an orderBy at the end, and can I reference the "total" alias I created in the selectRaw?
If you don't want to use selectRaw, you can also use the DB facade directly: DB::table('users')->select('city', DB::raw('count(*) as total'))->groupBy('city')->get();. It’s essentially the same result, but sometimes cleaner if you aren't using any model-specific logic.
I agree with Alex, but I usually stick to the Model approach (Answer 1) because it allows me to use Eloquent features like "Accessors" or "Appended attributes" on the results later on. It keeps the code feeling more "Laravel-like."
Sarah, yes you can! You can simply append ->orderBy('total', 'desc') to the query. Laravel is smart enough to recognize the alias you defined in your raw select. If you find yourself doing this often for complex reporting, you might also look into using orderByRaw('count(*) DESC') if you prefer not to rely on aliases