Laravel Eloquent get results grouped by days

php

eloquent

groupby

carbon

byNikita Barsukov·Aug 23, 2024

Leverage Laravel Eloquent to group by day as follows:

$dailyEvents = Event::selectRaw('DATE(created_at) as date, COUNT(*) as count')
                     ->groupBy('date') 
                     ->get();

Opt for selectRaw for efficiency and remarkable performance boost. Do remember to switch Event and created_at with your model and datetime column. This query results in a group of days each associated with its event count.

Turbocharge query with Carbon

Speedy DateTime Grouping: While grouping results, use DATE() function to exhibit efficient memory utilization and chop down the timestamp to a date – well-suited for grouping.

$dailyEvents = Event::selectRaw('DATE(created_at) as date, COUNT(*) as count') 
                     ->groupBy('date')
                     ->orderBy('date', 'DESC') 
                     ->get();

// "orderBy" walks into a bar, SQL bartender says: "I haven't seen you in ages". 😃

Master Carbon for Date Operations: Power your date operations with Laravel's Carbon extension. Subtract an interval, such as a month, right within your query for the flexibility of querying:

$recentEvents = Event::where('created_at', '>', \Carbon\Carbon::now()->subMonth())
                     ->get();

// Time flies when you're having fun, even in queries. ⏳

Fetch and transform data efficiently

Selecting Essential Columns Only: Fetch only the columns you require – you will save memory and give your application a speed boost.

Eloquent Grouping: Maintain readability and extendability of your queries with Eloquent's groupBy() method. It's vital for all maintainers – present and future.

Addressing daily grouping scenarios

Leveraging custom date formats

Data grouping by days sometimes calls for non-standard date formats. Use selectRaw() and DB::raw() for better format control:

$customFormatEvents = Event::selectRaw("DATE_FORMAT(created_at, '%Y-%m-%d') as formatted_date, COUNT(*) as count")
                           ->groupBy('formatted_date')
                           ->get();

// Because everyone likes their dates "well-formatted". 😉

Boosting reusability with local scopes

Defining local scopes in Eloquent promotes encapsulation and reusability of queries. Simply put, make your favorite queries a shorthand call away.

Scope methods like scopeByDay can be formulated to add on to Eloquent's query builder, offering you an automated grouping mechanism:

public function scopeByDay($query)
{
    return $query->selectRaw('DATE(created_at) as date, COUNT(*) as count')
                 ->groupBy('date');
}

// Usage:
$eventsByDay = Event::byDay()->get();