Hello I am trying to get data from database based on Month for Last 12 Year. I able to get the data monthwise and trying to get "0" if data is not present in any specific month. Below is the code. I am new to Laravel and self learner. Your help really appreciated

Controller Code

 $results        =   Dairyexpense::select(
                                        DB::raw('DATE_FORMAT(expensesdate, "%M %Y") as "month_name", SUM(amount) as "amount", max(expensesdate) as expensesdate')
                                        )
                                        ->whereBetween('expensesdate', [$start_date, $end_date])
                                        ->groupBy('month_name')
                                        ->orderBy('expensesdate', 'desc')
                                        ->get()
                                        ->keyBy('month_name')
                                        ->map(function ($item) {
                                            $item->date = Carbon::parse($item->date);
                                            return $item;
                                        });

                $start    = new DateTime($start_date);
                $end      = new DateTime($end_date);
                
                $interval = new DateInterval('P1M');
                $period = new DatePeriod($start, $interval, $end);

                $rr = array_map(function ($datePeriod) use ($results) {
                    $date = $datePeriod->format('Y-m-d');
                    return $results->has($date) ? $results->get($date)->total : 0;
                
                }, iterator_to_array($period));

CodePudding user response：

you need a different date format here

$date = $datePeriod->format('Y-m-d');

Like this ('F Y')

CodePudding user response：

                $results        =   Dairyexpense::whereBetween('expensesdate', [$start_date, $end_date])
                                    ->groupBy('expensesdate')
                                    ->orderBy('expensesdate')
                                    ->get([
                                        DB::raw('DATE_FORMAT(expensesdate, "%M %Y") as expensesdate'),
                                        DB::raw('SUM(amount) as total')
                                    ])
                                    ->keyBy('expensesdate')
                                    ->map(function ($item) {
                                        $item->date = Carbon::parse($item->date);
                                        return $item;
                                    });

                                        
                $start    = new DateTime($start_date);
                $end      = new DateTime($end_date);
                
                $interval = new DateInterval('P1M');
                $period = new DatePeriod($start, $interval, $end);

                $rr = array_map(function ($datePeriod) use ($results) {
                    $date = $datePeriod->format('F Y');
                    return $results->has($date) ? $results->get($date)->total : 0;
                
                }, iterator_to_array($period));