Counting and Grouping Database Records Using Laravel

  • April 04, 2016

Welcome to the incredibly popular Easy Laravel 5 companion blog. To celebrate the new edition's release (updated for Laravel 5.5!) use the discount code easteregg to receive 20% off the book or book/video package! » Buy the book

It's often useful to count the number of times a column value appears in a database table, however doing so using Laravel's fantastic ORM might not be so obvious. In this post you'll learn how to use Laravel's Query builder to easily group records according to a specific column and order the results.

Suppose you were busy expanding the TODOParrot administrative dashboard, and wanted to create a screen which identified the application's most active users for a given month and date. To keep matters simple, I'll define "active" in terms of the number of lists each user creates during the specified period. Therefore the query must consist of the following clauses:

  • It must determine the number of times each user's ID appears (The Todolist and User model maintain a belongsTo/hasMany relationship, respectively).
  • It must constrain the date range by filtering on a given month and year.
  • It must group the results by the user_id column in order to know what criteria should be used to establish how the returned records should be counted.
  • It must order the results in descending fashion (most active users on top).

With these requirements defined, constructing the query is relatively straightforward:

$activeUsers = \DB::table('todolists')
    ->select('user_id', \DB::raw('count(*) as total'))
    ->whereYear('created_at', '=', 2016)->whereMonth('created_at', '=', 4)
    ->groupBy('user_id')
    ->orderBy('total', 'desc')
    ->get();

When executed, the following SQL will be run:

select `user_id`, count(*) as total 
from `todolists` 
where year(`created_at`) = ? and month(`created_at`) = ? 
group by `user_id` 
order by `total` desc"

The question marks are placeholders, since Laravel will use parameter binding in order to prevent SQL injection. At run time the supplied 2016 and 4 will be safely inserted into the query.

I hope this brief example helps the next time you're interested in tallying grouped records!