whereDate not working in foreach loop using Carbon duration

Hi there, appreciate some guidance here. i have the following codes:

// get the orders created in 6 days range
 $statOrders = Order::where('user_id', Auth::user()->id)
            ->whereBetween('created_at', [Carbon::now()->subdays(6)->format('Y-m-d'), Carbon::now()->addDays(1)->format('Y-m-d')]);

// get the duration date
$duration = CarbonPeriod::create(now()->subDays(6), now());

// loop for each day
foreach ($duration as $day) {
		$orderDuration = $statOrders->whereDate('created_at', $day)->get();
		$days[] = $day;
		 //$order[] = $orderDuration->count();
		 //$sale[] = $orderDuration->sum('total_amount');
}

the $orderduration fails to return any orders. but if i try hardcoded the $day to '2022-02-14', i can get the orders correctly. i have also tried with $day->format('Y-m-d') but still the same.

my objective is to get the orders temporarily in the loop so i can get the order and sale amount per day in an array format

any idea why this happened?

MichalOravec

4 years ago

Level 75

$day is datetime, but created_at is just date in your case.

Get inspired by this solution:

https://stackoverflow.com/a/69179395/16868424

1 like

mudzao

4 years ago

Level 1

@MichalOravec thanks for the suggestion. i've refactored the code with only single query to db and then just getting the right info into a flat array. i havent finish the function but this portion now seems working now. thanks so much!

        $statOrders = Order::where('user_id', Auth::user()->id)
            ->whereBetween('created_at', [Carbon::now()->subdays(6)->format('Y-m-d'), Carbon::now()->addDays(1)->format('Y-m-d')])
            ->selectRaw('date(created_at) as date, count(id) as count, sum(total_amount) as sale, sum(total_commission) as commission')
            ->groupBy('date')
            ->get();
        
        $duration = CarbonPeriod::create(now()->subDays(6), now());

        foreach ($duration as $day) {
            $day= $statOrders->where('date', $day->format('Y-m-d'))->first() ;
            $days[] = $day->format('Y-m-d');
            $order[] = $day->count ?? 0;
            $sale[] = $day->sale ?? 0;
            $commission[] = $day->commission ?? 0;
        }

Please or to participate in this conversation.