Laravel Eloquent, Group by Month/Year

Laravel Eloquent, group by month/year

You can try as:

->select(DB::raw('count(id) as `data`'), DB::raw("DATE_FORMAT(created_at, '%m-%Y') new_date"),  DB::raw('YEAR(created_at) year, MONTH(created_at) month'))
->groupby('year','month')
->get();

Laravel Carbon Group by Month

You can use groupBy() method with closure:

 $months = NewsItem::groupBy(function($d) {
     return Carbon::parse($d->created_at)->format('m');
 })->get();

Or get data first and then use groupBy() on the Eloquent collection:

 $months = NewsItem::get()->groupBy(function($d) {
     return Carbon::parse($d->created_at)->format('m');
 });

Laravel - DB group by month

There is section in the docs, where you can find an information about the groupBy method - method

In your code you can write this():

$user_list = User::select('name','email','created_at')
        ->orderBy('created_at')->get();// get User collection
$user_list->groupBy(function ($item, $key) {
   return $item->created_at->format('m');
});

Fetching a summary grouped by month with Eloquent

The way I read your question is as following: The second query is executed in a loop with results from the first one. Is that right? In my answer I have explained a way to execute the second query just one time instead of in a loop. You'd still have to execute the first query once.

So, I think that you are better of using the Php collection methods:

$results = Gk::where('user_id',$request->user_id)
    ->orderBy('created_at','desc')
    ->get()
    ->groupBy(function (Gk $item) {
        return $item->created_at->format('Y-m');
    });

The groupBy method has to return an attribute on which you want to group the elements. For this example I think that using a yyyy-mm format will do fine.

Reference: https://laravel.com/docs/5.5/collections#method-groupby

Edit: Maybe you can also get rid of the orderBy method call because you are grouping by afterwards:

$results = Gk::where('user_id',$request->user_id)
    ->get()
    ->groupBy(function (Gk $item) {
        return $item->created_at->format('Y-m');
    });

Edit 2: To combine the information of the two queries, you could do something like the following:

$results = Gk::where('user_id',$request->user_id)
    ->get()
    ->groupBy(function (Gk $item) {
        return $item->created_at->format('Y-m');
    })->map(function(Collection $rows) {
        return [
            'sum(ton)' => $rows->sum('ton'),
            'total' => $rows->count(),
            'sum(w)' => $rows->sum('w'),
            'rows' => $rows
        ];
    );

Note that I have omitted a few of the selected columns in your first query because they are not unique in the given group by. But feel free to add any logic in the map function. Because we use map() after groupBy, every call of map() will receive a collection of items for one month. You can that use that fabulous collection magic to calculate all values that you need and reduce the number of queries to just one.

Group by month or week with Laravel eloquent

Add DB::raw(WEEK(data_imports.data_import) AS weeknumber) and then replace ->groupBy('data_imports.data_import') with ->groupBy('weeknumber') and the same with MONTH() function if you want to group by month: add another select column DB::raw(MONTH(data_imports.data_import) AS monthnumber) and replace ->groupBy('data_imports.data_import') with ->groupBy('monthnumber'). So the whole Eloquent query with week grouping would be:

$rows =  AtivosExtrato::select('titulo_id', DB::raw('SUM(valor_bruto_atual) AS valor'), 'data_imports.data_import as created_at', DB::raw('WEEK(data_imports.data_import) AS weeknumber'))
->join('titulos','titulo_id', '=', 'titulos.id' )
->join('representantes','representante_id', '=', 'representantes.id' )
->join('data_imports','data_import_id', '=', 'data_imports.id' )
->where('user_id', Auth::user()->id) 
->whereBetween('data_imports.data_import', [$request->input('start_date'), $request->input('end_date')])
->groupBy('titulos.nome_titulo')
->groupBy('weeknumber')
->orderBy('data_import')
->orderBy('titulos.nome_titulo')
->get(); 

Laravel Group Query Result by Day on Month-Year (show all date if not exist date)

1. Create a new collection via php artisan make:collection \\App\\Collections\\ExampleCollection
2. Do the following:

<?php

namespace App\Collections;

use DatePeriod;
use Illuminate\Database\Eloquent\Collection;
 
class ExampleCollection extends Collection
{
    public function withDefaults()
    {
        $date = $this->sortBy('day')[0]->day->firstOfYear();
        $days = array_map(function($day) {
            return $day->format('Y-m-d');
        }, [...new DatePeriod("R11/{$date->toIso8601ZuluString()}/P1D")]); // e.g. 2021-01-01T00:00:00Z
        $collection = array_fill_keys(array_fill_keys($days, []));
        foreach($this as $item) {
            $collection[$item->day->format('Y-m-d')] = $item;
        }
        return collect($collection);
    }
}

3. Inside your ExampleTable model add this:

    public function newCollection(array $models = [])
    {   
        return new ExampleCollection($models);
    }

4. Use it like this:

$results = ExampleTable::select(
    DB::raw("DATE_FORMAT(created_at,'%Y-%M-%d') as day"),
    DB::raw('sum(result) as sumAll')
)
->whereMonth("created_at", '05')
->whereYear("created_at", '2021')
->groupBy('day')
->get()
->withDefaults();

Group by month and order the dates on Laravel Eloquent

Try with max function and ->orderBy('createdAt', 'desc').

$months = \App\DATA::select(
       DB::raw('sum(order_value) as `sums`'),
       DB::raw("DATE_FORMAT(created_at,'%M %Y') as months"),
       DB::raw('max(created_at) as createdAt')
)
      ->where("created_at", ">", \Carbon\Carbon::now()->subMonths(6))
      ->orderBy('createdAt', 'desc')
      ->groupBy('months')
      ->get();

---

Related Topics