Laravel eloquent query with collection optimization

I have the following queries in my controller to show some statistics about orders paid, and unpaid and the respective amounts (totals), by year. Yet, I feel that the queries I'm using may be optimized but I can't figure out a better way to do it. Here's what I have:

public function index(Order $order)
{
	$orders = Order::all()->groupBy(function ($order) {
		return $order->created_at->format('Y');
	})->map(function ($year) {
		return $year->sum('total');
	});
	
	$unpaid_orders = Order::all()->where('payment_confirmed', 0)->groupBy(function ($order) {
		return $order->created_at->format('Y');
	})->map(function ($year) {
		return $year->sum('total');
	});
	
	$paid_orders = Order::all()->where('payment_confirmed', 1)->groupBy(function ($order) {
		
		return $order->payment_confirmed == false ? $order->year : $order->created_at->format('Y');
	})->map(function ($year) {
		return $year->sum('total');
	});
	
	return view('orders.index', compact('orders', 'unpaid_orders', 'paid_orders'));
}

Above I'm querying the Order model three times which it seems that could be optimized.

Do $collection = Order::all(); once and use $collection wherever you need it.

public function index(Order $order)
{
    $collection = Order::all();

    // OR

    $collection = Order::select(['id', 'payment_confirmed', 'total', 'year', 'created_at'])->get();

    $orders = $collection->groupBy(fn ($order) => $order->created_at->format('Y'))
        ->map(fn ($order) => $order->sum('total'));

    $unpaid_orders = $collection->where('payment_confirmed', 0)
        ->groupBy(fn ($order) => $order->created_at->format('Y'))
        ->map(fn ($order) => $order->sum('total'));

    $paid_orders = $collection->where('payment_confirmed', 1)
        ->groupBy(
            fn ($order) => $order->payment_confirmed == false
                ? $order->year
                : $order->created_at->format('Y')
        )
        ->map(fn ($order) => $order->sum('total'));

    return view('orders.index', compact('orders', 'unpaid_orders', 'paid_orders'));
}

To optimize the query in the provided code using scopes, you can define scopes in your Order model. Scopes allow you to encapsulate reusable query logic, making it easier to optimize and maintain your code. Here's an optimized version of the code using scopes:

First, let's define the scopes in your Order model:

class Order extends Model
{
    // ...

    public function scopeUnpaid($query)
    {
        return $query->where('payment_confirmed', 0);
    }

    public function scopePaid($query)
    {
        return $query->where('payment_confirmed', 1);
    }

    public function scopeGroupByYear($query)
    {
        return $query->groupBy(function ($order) {
            return $order->created_at->format('Y');
        });
    }
}

Now, let's modify the index method to utilize these scopes:

public function index(Order $order)
{
    $orders = Order::groupByYear()->get()->map(function ($year) {
        return $year->sum('total');
    });

    $unpaid_orders = Order::unpaid()->groupByYear()->get()->map(function ($year) {
        return $year->sum('total');
    });

    $paid_orders = Order::paid()->groupByYear()->get()->map(function ($year) {
        return $year->sum('total');
    });

    return view('orders.index', compact('orders', 'unpaid_orders', 'paid_orders'));
}

By using scopes, we can chain the necessary conditions and group by logic directly on the Order model, resulting in cleaner and more optimized code.