Efficiently load or cache view, that is based on many nested relations

Question

I have unlimited category, sub-category, sub-sub-category etc..

The structure:

main
--main1
-----main2 ( child of main1 )
-----main3 ( child of main1 )
--main4
test

My code:

in Category.php model

public function categories()
{
    return $this->hasMany(Category::class);
}

public function childrenCategories()
{
    return $this->hasMany(Category::class);
}

in index.blade.php:

<select>
    @foreach ($categories as $category)
        <option value="{{ $category->id }}">{{ $category->category_name }}</option>

        @foreach ($cat->childrenCategories as $childCategory)
            @include('partials._child_categroy', ['child_category' => $childCategory])
        @endforeach

    @endforeach
</select>

in _child_categroy.blade.php

<option value="{{ $child_category->id }}">--- {{ $child_category->category_name }}</option>

@if ($child_category->categories)
    @foreach ($child_category->categories as $childCategory)
        @include('partials._child_categroy', ['child_category' => $childCategory])
    @endforeach
@endif

So good so far but when I open the debugger there are three queries for each level of category there is loop! here is my problem what if my chain getting bigger and bigger then the foreach will be in increase and this is bad for performance!

Any suggestions please ?

Why do you need categories() AND childrenCategories(), isn't that the same? I mean categories() called on a $category already delivers child categories.. — wschopohl, Jul 21 '20 at 18:34
@wschopohl I tried to depend on just childrenCategories() the main category would show in loop like just show main and leave test — , Jul 21 '20 at 18:38
What you are looking to achieve is incredibly difficult (imo as an sql middler). You are looking to recursively self join categories until there is no further category to join. In a single query this is either impossible, or extremely complicated. You can check this out to see if you can make it work for your situation. https://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join. So I would say your extra hits to the DB are either ok, unless performance becomes a real issue, or entirely necessary if a single query approach is impossible. — Kurt Friars, Jul 21 '20 at 21:01
@KurtFriars Oh! to be honest I'm not an export in SQL and just take a look at the link, and didn't get anything :( — , Jul 21 '20 at 21:41
@KurtFriars in my tests laravel seemed to perform at least quite decent with eager loading. As you can see below it managed to get the amount of requests to db down by quite a margin. But yeah, getting it all in one request, I wouldn't know how to do that laravel wise .. — wschopohl, Jul 23 '20 at 10:30

wschopohl · Accepted Answer · 2020-09-25T10:09:42.513

This question and it's underlying problems awoke my interest and so I wanted to find out more about the whole matter. I created a test scenario myself.

Optimization

First some optimizations to the code of the blade templates:

// index.blade.php
<select>
    @include('categories.options', ['categories' => $categories, 'level' => 0])
</select>

// options.blade.php
@foreach ($categories as $category)
    <option value="{{ $category->id }}">{{ str_repeat("--", $level) }} {{ $category->name }}</option>
    @include('categories.options', ['categories' => $category->categories, 'level' => $level+1])
@endforeach

I then generated a database with about 5000 nested categories, 8 levels deep, to test the load times. My assumption was that if you add eager loading to the Category Model you can optimize the load time:

// Category.php

// this eager loads ALL nested relations using levels + 1 queries
protected $with = ['categories'];
// this is not needed and doesn't make any difference
protected $with = ['categories.categories'];

Here are the results:

                       Time  Queries  Memory
--------------------------------------------------
  No eager loading   12,81 s     5101   112MB
with eager loading    1,49 s        9    31MB
 2 x eager loading    1,54 s        9    31MB

Caching               0,08 s        0     4MB

(stats recorded mainly with debugbar)

So as you can see eager loading definitely makes sense. It's also enough to put a single $with = ['categories'] in the model and laravel will eager load all the nested realtionships at once - neat!

Caching

So the only real solution, to get the site loading as fast as possible (that I could think of) is caching.

// create new job in console
php artisan make:job RenderCategoryView

// RenderCategoryView.php
public function handle() {
    // get all top level categories
    $categories = \App\Category::where('category_id', null)->get();
    $html = \View::make('categories.options', ['categories' => $categories, 'level' => 0])->render();
    file_put_contents(resource_path('views/categories/options_cache.blade.php'), $html);
    return true;
}

Now you can replace the @include of your blade template like this:

// index.blade.php
@include('categories.options_cache')

To test the generation of the options_cache file you can do:

php artisan tinker
\App\Jobs\RenderCategoryView::dispatchNow();

I also removed the now unnecessary database query before returning the index view and the new load time is 83 ms. Not Very surprising, because now everything is cached.

To automatically generate a new view cache once a category is created, edited or deleted you should include this in the respective Controller(s):

\App\Jobs\RenderCategoryView::dispatch();

Read more about how to dispatch jobs on queues and the like in the Laravel Docs.

My code for this test can be found on github.

Really, really nice answer. – Kurt Friars Jul 23 '20 at 10:43 — Kurt Friars, Jul 23 '20 at 10:43

Efficiently load or cache view, that is based on many nested relations

1 Answers1

Optimization

Caching