Laravel Eloquent: Grouping Data by Month/Year

Are you looking to group your data in Laravel using Eloquent? Do you want to group your data by month and year? In this blog post, we will explore how to achieve this using Laravel‘s powerful Eloquent ORM.

Table of Contents

Introduction

Laravel is a popular PHP framework known for its elegant syntax and powerful features. One of its key components is Eloquent, an ORM (Object-Relational Mapping) that simplifies database operations and makes working with databases a breeze.

Grouping Data by Month/Year

Let’s say you have a table in your database that contains records with a created_at timestamp column. You want to group these records by month and year to get aggregated data for each month. Here’s how you can do it using Laravel’s Eloquent:

$data = YourModel::selectRaw(‘count(id) as data‘)
->selectRaw(“CONCAT(MONTH(created_at), ‘-‘, YEAR(created_at)) as month“)
->groupBy(DB::raw(‘YEAR(created_at)’), DB::raw(‘MONTH(created_at)’))
->get();

Let’s break down the above code:

We start by selecting the count of records and aliasing it as data. This will give us the aggregated count for each month.
Next, we use the CONCAT function to combine the month and year values into a single string, separated by a hyphen. This will give us the desired format for the month.
We then group the results by the year and month using the groupBy method. This ensures that the records are grouped correctly.
Finally, we call the get method to retrieve the grouped data.

Example Output

The output of the above code will be an array of objects, with each object representing a month and its corresponding data. Here’s an example of how the output might look:

[
    {
        "data": 19215,
        "month": "11-2016"
    },
    {
        "data": 11215,
        "month": "12-2016"
    },
    ...
]

Using Carbon

codeuse CarbonCarbon;

$data = YourModel::selectRaw('count(id) as `data`')
    ->selectRaw("CONCAT(MONTH(created_at), '-', YEAR(created_at)) as `month`")
    ->groupBy(DB::raw('YEAR(created_at)'), DB::raw('MONTH(created_at)'))
    ->get()
    ->map(function ($item) {
        $item->month = Carbon::createFromFormat('m-Y', $item->month)->format('F Y');
        return $item;
    });

In this solution, we use the Carbon library to parse the month-year string and format it as “F Y” (e.g., “November 2016”) for better readability.

Using MySQL’s DATE_FORMAT

$data = YourModel::selectRaw(‘count(id) as data‘)
->selectRaw(“DATE_FORMAT(created_at, ‘%M %Y’) as month“)
->groupBy(DB::raw(‘YEAR(created_at)’), DB::raw(‘MONTH(created_at)’))
->get();

In this solution, we utilize MySQL’s DATE_FORMAT function directly in the query to format the month-year string as “Month Year” (e.g., “November 2016”).

Using Laravel’s Date Casting

If you have defined the created_at column in your model as a datetime type, you can leverage Laravel’s built-in date casting feature to simplify the grouping:

protected $casts = [
‘created_at’ => ‘datetime:Y-m’,
];

With this date casting, you can directly group the data without any additional formatting:

$data = YourModel::selectRaw(‘count(id) as data‘)
->groupBy(‘created_at’)
->get();

Using YEAR() and MONTH() Functions

This solution utilizes the YEAR() and MONTH() functions in the SELECT statement to extract the year and month separately, and then concatenates them using the CONCAT() function to get the desired format (e.g., “11-2016”).

Using Eloquent Accessors

If you want to manipulate the date format directly in your model, you can define an accessor method to return the formatted month-year string:

class YourModel extends Model
{
// …

public function getFormattedMonthAttribute()
{
    return date('m-Y', strtotime($this->created_at));
}

}

Then, you can use the accessor in your query like this:

$data = YourModel::selectRaw(‘count(id) as data‘)
->selectRaw(“CONCAT(month(created_at), ‘-‘, year(created_at)) as month“)
->groupBy(‘month’)
->get();

In this solution, the getFormattedMonthAttribute accessor formats the created_at date using the date function and returns it in the desired format (e.g., “11-2016”). The accessor can be used in the query to get the formatted month-year string

Conclusion

In this blog post, we explored how to group data by month and year using Laravel’s Eloquent ORM. By leveraging the power of Eloquent, you can easily perform complex database operations with just a few lines of code. Grouping data in Laravel has never been easier!

We hope you found this tutorial helpful. If you have any questions or suggestions, please feel free to leave a comment below.