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:
Usermodel maintain a belongsTo/hasMany relationship, respectively).
user_idcolumn in order to know what criteria should be used to establish how the returned records should be counted.
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
4 will be safely inserted into the query.
I hope this brief example helps the next time you're interested in tallying grouped records!