Is there a problem with the $monthlyInvoices variable?

This is my job. When I run this job the online invoices query doesn’t display anything, but what is intriguing is that when I swap the places of the $onlineInvoices and the $offlineInvoices variables the $onlineInvoices query works while the $offlineInvoices doesn’t. It worked fine when I redefined the $monthlyInvoices again below the $offline_sales variable, But I would like to know why this occurs.

<?php

namespace AppJobs;

use AppModelsInvoice;
use AppModelsInvoiceMonthlyStatistics;
use CarbonCarbon;
use CarbonCarbonPeriod;
use IlluminateBusQueueable;
use IlluminateContractsQueueShouldBeUnique;
use IlluminateContractsQueueShouldQueue;
use IlluminateDatabaseEloquentModel;
use IlluminateFoundationBusDispatchable;
use IlluminateQueueInteractsWithQueue;
use IlluminateQueueSerializesModels;

class InvoiceMonthlyStatisticsJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    /**
     * Create a new job instance.
     *
     * @return void
     */
    public function __construct()
    {
        //
    }

    /**
     * Execute the job.
     *
     * @return void
     */
    public function handle()
    {
        $period = CarbonPeriod::create('2021-08-18', '1 month', Carbon::now()->toDateString());
        foreach ($period as $date) {
            $monthlyInvoices = Invoice::
            whereRaw('YEAR(created_at) = ?', $date->format('Y'))
                ->whereRaw('MONTH(created_at) = ?', $date->format('m'));

            $total_sales = $monthlyInvoices
                ->sum('net_price');


            $offline_sales = $monthlyInvoices
                ->where('type', '=', 'offline')
                ->sum('net_price');

        
            $online_sales = $monthlyInvoices
                ->where('type', '=', 'online')
                ->sum('net_price');


            $invoiceStats = (new InvoiceMonthlyStatistics)->create([
                'total_sales' => $total_sales,
                'online_sales' => $online_sales,
                'offline_sales' => $offline_sales,
                'month' => $date->format('m'),
                'year' => $date->format('y')
            ]);
        }
        $invoiceStats->save();
    }
}

Answer

This is because $monthlyInvoices is a query builder.

When you add a where statement, it remains even after you called sum:

$offline_sales = $monthlyInvoices
                ->where('type', '=', 'offline') //here
                ->sum('net_price');

That’s the problem, because when you do:

$online_sales = $monthlyInvoices
                ->where('type', '=', 'online')
                ->sum('net_price');

->where('type', '=', 'offline') is still here, because you previously added it.

In a sense, your second query is the same as doing:

$online_sales = $monthlyInvoices
                ->where('type', '=', 'offline')
                ->where('type', '=', 'online')
                ->sum('net_price');

Now you probably see the problem here. One quick way to solve this is to clone the query builder before each query.

$offline_sales = (clone $monthlyInvoices)
                ->where('type', '=', 'offline')
                ->sum('net_price');

        
$online_sales = (clone $monthlyInvoices)
                ->where('type', '=', 'online')
                ->sum('net_price');

By cloning $monthlyInvoices, you are basically saying “don’t use the same query builder, but a new one that is identical”. This temporary query builder will only be used in this particular query and where('type', '=', 'offline') (or whatever you do) will not be “persisted” into the original $monthlyInvoices query builder.