Introduction
Laravel Eloquent ORM provides a beautiful, simple ActiveRecord implementation for working with your database. Each database table has a corresponding “Model” that is used to interact with that table. In this tutorial, we’ll delve into one of the powerful methods provided by the Eloquent ORM: groupBy().
The groupBy() method is used to group a result set by one or more columns. This method plays a key role in generating summaries from database records and is an essential tool for handling aggregated data.
Prerequisites
- Basic understanding of Laravel and Eloquent
- Laravel framework installed on your machine
- A database connection configured in your Laravel environment
Using groupBy(): The Basics
Let’s start with a simple example. Consider a table named orders where each order is associated with a user_id. To get the number of orders per user, you’d group the results by the user_id column like so:
$orders = Order::select('user_id', DB::raw('count(*) as total'))
->groupBy('user_id')
->get();
Output:
[
{"user_id": 1, "total": 10},
{"user_id": 2, "total": 7},
...
]The above query selects the user_id and a raw count of each user_id occurrence in the orders table, grouping the results by user_id.
Grouping by Multiple Columns
Now let’s say your orders also have a status column, and you want to get a count of each order status per user. You can group by multiple columns like this:
$orders = Order::select('user_id', 'status', DB::raw('count(*) as total'))
->groupBy('user_id', 'status')
->get();
Output:
[
{"user_id": 1, "status": "pending", "total": 5},
{"user_id": 1, "status": "completed", "total": 5},
{"user_id": 2, "status": "pending", "total": 4},
...
]This query groups the orders first by user_id and then by status, resulting in a count of each type of status for each user.
Advanced Grouping with Having
Sometimes, you might want to filter groups after they have been aggregated. Eloquent’s having() method complements groupBy() here.
$orders = Order::select('user_id', DB::raw('count(*) as total'))
->groupBy('user_id')
->having('total', '>', 5)
->get();
This will fetch all users who have more than 5 orders. The having() method is similar to the SQL HAVING clause.
Grouping with Relationships
If your model has relationships, you can also apply groupBy() in combination with joins. For example, if you want to count the orders for each user and join user information from the users table, you can do the following:
$orders = Order::select('users.name', DB::raw('count(*) as total'))
->join('users', 'orders.user_id', '=', 'users.id')
->groupBy('users.name')
->get();
This query joins the orders table with the users table, groups the results by user names, and counts the orders per user name.
Raw Expressions in Grouping
There might be occasions when you need to use raw expressions inside your groupBy() clause. Eloquent allows you to use DB::raw() for these cases. Here’s how you could group by the month of an created_at timestamp:
$orders = Order::select(DB::raw('YEAR(created_at) as year'), DB::raw('MONTH(created_at) as month'), DB::raw('count(*) as total'))
->groupBy(DB::raw('YEAR(created_at)'), DB::raw('MONTH(created_at)'))
->get();
This groups the orders by year and month and counts them.
Conclusion
groupBy() can significantly simplify the task of aggregating data based on certain criteria. It allows you to write clean and readable Eloquent queries without getting into the complexity of raw SQL queries. Whether you are grouping by a single column, multiple columns, or using advanced conditions, Eloquent’s groupBy() fits perfectly within the Laravel query building experience.
