laravel

Laravel: N+1. Each HomepageSection with type category calls getRecipes() and produces duplicate category/recipe queries

I have a homepage made of "sections" (HomepageSection). Each section can be one of: popular, latest, or category. For each section I call a getRecipes() method to get the recipes for that section.

The problem: every category section triggers its own Recipe query (and additional queries for dish_categories/cuisines), so the home page ends up doing many repeated SQL queries (N+1 style). I want to reduce the number of SQL queries to only a few (e.g. sections + one batched recipes query for all categories + one for popular/latest), not just rely on per-section cache.

I have ~7 homepage sections (some category, some popular, some latest).

Debugbar shows ~20+ SQL queries on page load.

For every category section I see a select ... from recipes where exists(select from dish_categories where recipes.dish_category_id = dish_categories.id and slug = '...') limit 4 — i.e. one recipe query per category section.

Also I see repeated queries for dish_categories/cuisines triggered by the eager loads in each separate get().

Below are the parts of my code.

create_homepage_sections_table:

Schema::create('homepage_sections', function (Blueprint $table) {
    $table->id();
    $table->string('name')->unique();
    $table->string('slug')->unique();
    $table->enum('type', ['popular','latest','category']);
    $table->string('category_slug')->nullable();
    $table->integer('order')->default(1);
    $table->boolean('visible')->default(true);
    $table->integer('limit')->default(4);
    $table->timestamps();
});

HomepageSection.php

class HomepageSection extends Model
{
    protected $fillable = [
        'name','slug','type','category_slug','order','visible','limit',
    ];

    public function scopeVisible(Builder $query): Builder
    {
        return $query->where('visible', true);
    }

    public function scopeOrdered(Builder $query): Builder
    {
        return $query->orderBy('order');
    }

    public function getRecipes(): Collection
    {
        // Cache per-section result
        return cache()->remember("homepage_section_recipes_{$this->id}", 60*60*24, function () {
            $query = Recipe::with(['dishCategory','cuisine'])
                ->select(['id','name','image','cuisine_id','dish_category_id']);

            return match($this->type) {
                'popular'  => $query->popular()->limit($this->limit)->get(),
                'latest'   => $query->latest()->limit($this->limit)->get(),
                'category' => $query->byCategory($this->category_slug)->limit($this->limit)->get(),
                default => collect(),
            };
        });
    }
}

HomeController.php

class HomeController extends Controller
{
    public function __invoke()
    {
        $homepageSections = HomepageSection::visible()->ordered()->get();

        $sections = $homepageSections->map(function ($section) {
            return [
                'id' => $section->slug,
                'title' => $section->name,
                'recipes' => $section->getRecipes(), // <-- each section calls getRecipes()
                'visible' => $section->visible,
                'order' => $section->order,
            ];
        });

        return view('index', compact('sections'));
    }
}

Recipe.php

class Recipe extends Model
{

    public function cuisine(): BelongsTo
    {
        return $this->belongsTo(Cuisine::class);
    }

    public function dishCategory(): BelongsTo
    {
        return $this->belongsTo(DishCategory::class);
    }

    public function scopePopular(Builder $query): Builder
    {
        return $query->withCount([
            'votes as likesCount' => fn (Builder $query) => $query->where('vote', 1),
            'votes as dislikesCount' => fn (Builder $query) => $query->where('vote', -1),
            'savedByUsers as savedCount',
        ])
            ->orderByDesc('savedCount')
            ->orderByDesc('likesCount')
            ->orderBy('dislikesCount')
            ->orderByDesc('created_at');
    }

    public function scopeLatest(Builder $query): Builder
    {
        return $query->orderBy('created_at', 'desc');
    }

    public function scopeByCategory(Builder $query, string $categorySlug): Builder
    {
        return $query->whereHas('dishCategory', function ($q) use ($categorySlug) {
            $q->where('slug', $categorySlug);
        });
    }
}

Would be grateful for your help

Best regards

Solution

  • Solution:
    Instead of fetching recipes inside each section’s method, load all recipes for all sections in a few batched queries, then assign them to the right sections in PHP.

    Updated HomeController

    class HomeController extends Controller
{
    public function __invoke()
    {
        // 1. Get all homepage sections
        $homepageSections = HomepageSection::visible()->ordered()->get();

        // 2. Collect category slugs & limits
        $categorySlugs = $homepageSections
            ->where('type', 'category')
            ->pluck('category_slug')
            ->unique()
            ->toArray();

        $popularLimit = $homepageSections->where('type', 'popular')->max('limit') ?? 4;
        $latestLimit  = $homepageSections->where('type', 'latest')->max('limit') ?? 4;

        // 3. Batch load recipes for each type

        // Popular recipes
        $popularRecipes = Recipe::with(['dishCategory', 'cuisine'])
            ->popular()
            ->limit($popularLimit)
            ->get();

        // Latest recipes
        $latestRecipes = Recipe::with(['dishCategory', 'cuisine'])
            ->latest()
            ->limit($latestLimit)
            ->get();

        // All category recipes in one query
        $categoryRecipes = collect();
        if (!empty($categorySlugs)) {
            $categoryRecipes = Recipe::with(['dishCategory', 'cuisine'])
                ->whereHas('dishCategory', fn($q) => $q->whereIn('slug', $categorySlugs))
                ->get()
                ->groupBy(fn($recipe) => $recipe->dishCategory->slug);
        }

        // 4. Assign recipes to sections
        $sections = $homepageSections->map(function ($section) use ($popularRecipes, $latestRecipes, $categoryRecipes) {
            return [
                'id'      => $section->slug,
                'title'   => $section->name,
                'recipes' => match ($section->type) {
                    'popular'  => $popularRecipes->take($section->limit),
                    'latest'   => $latestRecipes->take($section->limit),
                    'category' => $categoryRecipes->get($section->category_slug, collect())->take($section->limit),
                    default    => collect(),
                },
                'visible' => $section->visible,
                'order'   => $section->order,
            ];
        });

        return view('index', compact('sections'));
    }
}

    How this fixes N+1:

    
$sections = cache()->remember('homepage_sections_data', 3600, function () use (...) {
    // batch query logic here
});