Optimizing Eloquent Queries with Eager Loading and Model Logic Extraction

Introduction

This post explores how to optimize Eloquent queries in a Laravel application by addressing N+1 query problems and extracting shared logic into the model layer. We'll focus on improving performance when filtering data in admin tables.

The N+1 Query Problem

The N+1 query problem occurs when your application executes one database query to fetch a set of records, and then executes additional queries for each of those records to fetch related data. In our case, Tenant::all() was causing an N+1 query when filtering by plan because the subscriptions were being loaded lazily for each tenant.

Eager Loading Subscriptions

To resolve the N+1 query problem, we can use eager loading to fetch the subscriptions along with the tenants in a single query. This reduces the number of database queries from N+1 to 1, significantly improving performance.

$tenants = Tenant::with('subscriptions')->get();

Extracting Shared Logic to the Model

We identified duplicated logic in TenantResource and UserResource related to filtering plans. To promote code reuse and maintainability, we extracted this logic into the Plan model.

getActiveFilterOptions()

This method provides the options for filtering plans, ensuring consistency across different resources.

// Plan.php

public static function getActiveFilterOptions(): array
{
    return self::query()->pluck('name', 'slug')->toArray();
}

getTenantIdsForPlanSlug()

This method retrieves the tenant IDs associated with a specific plan slug, centralizing the filtering logic in the Plan model.

// Plan.php

public static function getTenantIdsForPlanSlug(string $planSlug): array
{
    return Tenant::whereHas('subscriptions', function ($query) use ($planSlug) {
        $query->where('plan_slug', $planSlug);
    })->pluck('id')->toArray();
}

Benefits

• Improved Performance: Eager loading eliminates the N+1 query problem, reducing database load and improving response times.
• Code Reusability: Extracting shared logic into the Plan model promotes code reuse and reduces duplication.
• Maintainability: Centralizing filtering logic in the model makes it easier to update and maintain.

Next Steps

Consider implementing caching strategies for frequently accessed filter options to further improve performance. Additionally, explore using query scopes to encapsulate common filtering logic within your models.