Laravel date group by year and by months

Question

i've a problem with a laravel 8 query. I've a table called partita_pagamenti, and my model is PartitaPagamenti.php. From my table i need to take this fields 'pagatoImposta','speseTabellari','spesePieLista','pagatoMora', 'dataPagamento' and group the results. I have difficulty grouping items by year and then grouping them by month. The data represents payments, so I would need to get a structure like this:

    2021 -> Jan -> payment list for January 2021
         -> Feb -> payment list for February 2021
            etc
    2020 -> Jan -> .....
              ..........

I tried this query

$this->report = PartitaPagamento::on($this->connection)->select('pagatoImposta','speseTabellari','spesePieLista','pagatoMora', 'dataPagamento')->get()->groupBy(function($val) {
            return Carbon::parse($val->dataPagamento)->format('Y');
        });

        $this->report = $this->report->groupBy(function($val) {
            return Carbon::parse($val->dataPagamento)->format('M');
        })->toArray();

but it doesn't work as I want, do you have any suggestions?

Answer 1

I've tried to print the values on a table and everything works fine except for the values in the red box in the image. Those values should not be printed there, but of those values only the value 5 should be in row 2019 and column October. This is the code with which I create the body of the table.

My Code:

@if(!empty($report))
                  @foreach ($report as $year => $months)
                    <tr>
                        <td>{{ $year }}</td>
                          @foreach ($months as $month => $payments)

                                      @if($month === 'Jan' && isset($payments))
                                        <?php $pagamenti = null; ?>
                                        @foreach ($payments as $payment)
                                            <?php $pagamenti=$pagamenti+$payment['pagatoImposta']; ?>
                                        @endforeach
                                        <td>  {{$pagamenti }}</td>
                                      @else
                                        <td>0</td>
                                      @endif

                                      @if($month === 'Feb' && isset($payments))
                                        <?php $pagamenti = null; ?>
                                        @foreach ($payments as $payment)
                                            <?php $pagamenti=$pagamenti+$payment['pagatoImposta']; ?>
                                        @endforeach
                                        <td>  {{$pagamenti }}</td>
                                      @else
                                        <td>0</td>
                                      @endif

                                      @if($month === 'Mar' && isset($payments))
                                        <?php $pagamenti = null; ?>
                                        @foreach ($payments as $payment)
                                            <?php $pagamenti=$pagamenti+$payment['pagatoImposta']; ?>
                                        @endforeach
                                        <td>  {{$pagamenti }}</td>
                                      @else
                                        <td>0</td>
                                      @endif

                                      @if($month === 'Apr' && isset($payments))
                                        <?php $pagamenti = null; ?>
                                        @foreach ($payments as $payment)
                                            <?php $pagamenti=$pagamenti+$payment['pagatoImposta']; ?>
                                        @endforeach
                                        <td>  {{$pagamenti }}</td>
                                      @else
                                        <td>0</td>
                                      @endif

                                      @if($month === 'May' && isset($payments))
                                        <?php $pagamenti = null; ?>
                                        @foreach ($payments as $payment)
                                            <?php $pagamenti=$pagamenti+$payment['pagatoImposta']; ?>
                                        @endforeach
                                        <td>  {{$pagamenti }}</td>
                                      @else
                                        <td>0</td>
                                      @endif

                                      @if($month === 'Jun' && isset($payments))
                                        <?php $pagamenti = null; ?>
                                        @foreach ($payments as $payment)
                                            <?php $pagamenti=$pagamenti+$payment['pagatoImposta']; ?>
                                        @endforeach
                                        <td>  {{$pagamenti }}</td>
                                      @else
                                        <td>0</td>
                                      @endif

                                      @if($month === 'Jul' && isset($payments))
                                        <?php $pagamenti = null; ?>
                                        @foreach ($payments as $payment)
                                            <?php $pagamenti=$pagamenti+$payment['pagatoImposta']; ?>
                                        @endforeach
                                        <td>  {{$pagamenti }}</td>
                                      @else
                                        <td>0</td>
                                      @endif

                                      @if($month === 'Aug' && isset($payments))
                                        <?php $pagamenti = null; ?>
                                        @foreach ($payments as $payment)
                                            <?php $pagamenti=$pagamenti+$payment['pagatoImposta']; ?>
                                        @endforeach
                                        <td>  {{$pagamenti }}</td>
                                      @else
                                        <td>0</td>
                                      @endif

                                      @if($month === 'Sep' && isset($payments))
                                        <?php $pagamenti = null; ?>
                                        @foreach ($payments as $payment)
                                            <?php $pagamenti=$pagamenti+$payment['pagatoImposta']; ?>
                                        @endforeach
                                        <td>  {{$pagamenti }}</td>
                                      @else
                                        <td>0</td>
                                      @endif

                                      @if($month === 'Oct' && isset($payments))
                                        <?php $pagamenti = null; ?>
                                        @foreach ($payments as $payment)
                                            <?php $pagamenti=$pagamenti+$payment['pagatoImposta']; ?>
                                        @endforeach
                                        <td>  {{$pagamenti }}</td>
                                      @else
                                        <td>0</td>
                                      @endif

                                      @if($month === 'Nov' && isset($payments))
                                        <?php $pagamenti = null; ?>
                                        @foreach ($payments as $payment)
                                            <?php $pagamenti=$pagamenti+$payment['pagatoImposta']; ?>
                                        @endforeach
                                        <td>  {{$pagamenti}}</td>
                                      @else
                                        <td>0</td>
                                      @endif

                                      @if($month === 'Dec' && isset($payments))
                                        <?php $pagamenti = null; ?>
                                        @foreach ($payments as $payment)
                                            <?php $pagamenti=$pagamenti+$payment['pagatoImposta']; ?>
                                        @endforeach
                                        <td>  {{$pagamenti }}</td>
                                      @else
                                        <td>0</td>
                                      @endif
                                      <td>totale</td>
                          @endforeach        
                    </tr>
                  @endforeach
                @endif

Here the shot: TableScreen

Answer 2

The first grouping is correct. On the second one, you need to group it for each year.

$this->report = PartitaPagamento::on($this->connection)
    ->select('pagatoImposta','speseTabellari','spesePieLista','pagatoMora', 'dataPagamento')
    ->get()
    ->groupBy(function ($val) {
        return Carbon::parse($val->dataPagamento)->format('Y');
    });

$this->report = $this->report
    ->map(function ($values) {
        return $values->groupBy(function ($val) {
            return Carbon::parse($val->dataPagamento)->format('M');
        });
    })
    ->toArray();

How to loop thru the results:

@foreach ($report as $year => $months)
   @foreach ($months as $month => $payments)
       @foreach ($payments as $payment)
           //
       @endforeach
   @endforeach
@endforeach