Optimizing Laravel Eloquent Queries

Recently, I was working on a project where I identified that a significant number of database queries were being executed, leading to performance issues. Especially when dealing with relationships, one common performance issue developers face is the N+1 query problem. This occurs when the application makes multiple database queries to fetch related data in a loop, significantly slowing down the response time.

In this article, I'll explain and share how to optimize such queries with a specific example.

The Problem

In the below Laravel API resource, we were trying to calculate the sum of a field (qty_donated, qty_sold, etc) for the merchant. Initially, the code looked like this:

class MerchantResource extends JsonResource
{
    public function toArray(Request $request): array
    {
        return [
            // ...
            'insights' => $this->whenLoaded(
                'products',
                function () {
                    return [
                        'donated' => $this->products()->sum('qty_donated'), 
                        'remainingForDonation' => $this->products()->where('donatable', 1)->sum('qty_for_donation'),

The key issue here is the use of $this->products()->sum('qty_donated') instead of $this->products->sum('qty_donated'). Here, $this->products() initiates a new query builder every time it is called. So, even though the relationship might already be loaded, this line of code ignores that and performs a fresh query to calculate the sum, leading to an N+1 query problem.

The Correct Approach

To optimize the query and ensure we’re using the loaded relationship data, the code should be written as:

$this->whenLoaded(
    'products',
    function () {
        return [
            'donated' => $this->products->sum('qty_donated')
        ];
    }
),

// OR we can use the more concise arrow function syntax:

$this->whenLoaded(
    'products',
    fn() => [
        'donated' => $this->products->sum('qty_donated')
    ] 
),

Using an Aggregated Relationship

Alternatively, we could create another relationship method in the Merchant model to handle the aggregation of product data. This approach ensures that the necessary data is fetched in a single query, avoiding multiple queries entirely.

class Merchant extends Model
{
    public function products()
    {
        return $this->hasMany(Product::class);
    }

    public function productInsights()
    {
        return $this->hasOne(Product::class)
            ->selectRaw('merchant_id,
                SUM(qty_donated) as totalDonatedQty,
                SUM(CASE WHEN donatable = 1 THEN qty_for_donation ELSE 0 END) as remainingForDonation')
            ->groupBy('merchant_id');
    }

// Then, update the MerchantResource to use this new method:

class MerchantResource extends JsonResource
{
    public function toArray(Request $request): array
    {
        return [
            // ...
            'insights' => $this->whenLoaded(
                'productInsights', 
                fn() => [
                    'donated' => $this->productInsights->totalDonatedQty 
                ]

Eager Loading Relationship in API Resources Collections

To fetch the merchants along with their aggregated product insights, we should ensure that the relationships are loaded correctly. Here’s how to do it:

Route::get('/merchants', function () {
    return MerchantResource::collection(
        Merchant::with('productInsights')->get()
    );
});

That's all for today! I hope you found this article helpful. Thank you for reading.