Laravel category tree. How to reduce the number of queries in the database?

Question

MySQL

In general, there is a table of categories .
Model code

class ProductCategory extends Model
{
    protected $table = 'product_categories';

    public function ProductCategory(){
        return $this->hasMany($this, 'parent_id');
    }

    public function rootCategories(){
        return $this->where('parent_id', 0)->with('ProductCategory')->get();
    }

}

Controller code

class ProductCategoryController extends Controller
{
    public function index(ProductCategory $productCategory){
        $rootCategories = $productCategory->rootCategories();
        return view('layouts.catalog', ['rootCategories' => $rootCategories,]);
    }
}

Template code 'layouts.catalog'

<h1>Catalog</h1>
                <ul>
                    @foreach($rootCategories as $rootCategory)
                        <li>{{ $rootCategory->name }}</li>
                        @if($rootCategory->ProductCategory->count() > 0)
                            @include('layouts.partials.treeChildMenu', ['categories' => $rootCategory->ProductCategory])
                        @endif
                    @endforeach
                </ul>

And the template code is 'layouts.partials.treeChildMenu'

<ul>
    @foreach($categories as $category)
        <li>{{ $category->name }}</li>
        @if($category->ProductCategory->count() > 0)
            @include('layouts.partials.treeChildMenu', ['categories' => $category->ProductCategory])
        @endif
    @endforeach
</ul>

Everything seems to be fine
. But the number of requests to the database scares me. Can this be optimized somehow? Because when adding new items, the menu of requests becomes more and more.

Answer 1

Select everything with one request and build a tree with a recursive function, if PHP has not been forgotten yet.
example

Answer 2

If you're using PostgreSQL, look into recursive subqueries .
If it's not PostgreSQL, then you can try building a tree from "raw data" directly in PHP (as ThunderCat tells you ).
But in general, personally, I would first evaluate the frequency of category changes in the catalog and the total number of categories (data volume), and in accordance with this information, I would choose a more appropriate model for storing trees in the database. There are four
such basic models : Adjacency list, Nested Sets, Materialized Path and Closure Tables (can be considered as development of Adjacency List) plus their variations.
Each type of model can serve to optimize data fetching from tree structures in the database.
There are also libraries for Laravel:
Nested Set
Closure Tables
For Adjacency List (your current implementation), just sort the data yourself, or try this package .

Answer 3

I would advise you to use a Nested Set tree instead of a regular tree. In pure PHP, how this is done is well written here www.getinfo.ru/article610.html. There is a good package for Laravel https://github.com/etrepat/baum (I use it myself).
An example of my table
Advantages of this approach - the entire branch can be obtained with one query.
Cons More complicated insert and update.
For some time now, I have been storing directories and pages in just such a tree - to get bread crumbs on a page - 1 request. Get directory tree - 1 request.

Answer 4

Add a couple more levels of loading with('ProductCategory', 'ProductCategory.ProductCategory', 'ProductCategory.ProductCategory.ProductCategory') to with, otherwise you have lazy loading with a large number of requests starting from the second level. There will be a couple of extra requests, but this is not critical for most projects.
If you load the entire tree, then you can select it as a whole, and then collect this tree on the php side.

Answer 5

I used to do this too (normal AL and queries to the database recursively). Brakes started on the kilometer tree. Altered on single request variant as already advised. In practice, it turned out that the brakes were not because of this, but because of the rendering of the tree. On a server with mysql_cache configured and a rarely changing category table, the difference between 1 and 200 requests turned out to be nonsense, but it is there. + someone will be "lucky" to wait for the tree when the cache is reset.
I'll attach my version (just for eloquent), two classes, add them somewhere and rename them as you need.

class Tree
{
    private $builder;

    private $parentIdFieldName = 'parent_id';

    private $nodesById = [];
    private $nodesByParent = [];

    public function __construct($builder)
    {
        $this->builder = $builder;
    }

    public function parentIdField($name)
    {
        $this->parentIdFieldName = $name;

        return $this;
    }

    /**
     * @return Output
     */
    public function get()
    {
        $nodes = $this->builder->get();

        foreach ($nodes as $node) {
            $this->nodesById[$node->id] = $node;
            $this->nodesByParent[$node->{$this->parentIdFieldName}][] = $node;
        }

        $output = new Output;

        $output->parentIdFieldName = $this->parentIdFieldName;
        $output->nodesById = $this->nodesById;
        $output->nodesByParent = $this->nodesByParent;

        return $output;
    }
}

class Output
{
    public $parentIdFieldName = 'parent_id';

    public $nodesById = [];
    public $nodesByParent = [];

    public function getNode($id)
    {
        if (!isset($this->nodesById[$id])) {
            $this->nodesById[$id] = [];
        }

        return $this->nodesById[$id];
    }

    public function getSubnodes($id)
    {
        if (!isset($this->nodesByParent[$id])) {
            $this->nodesByParent[$id] = [];
        }

        return $this->nodesByParent[$id];
    }

    private $branch;

    public function getBranch($id))
    {
        if (isset($this->nodesById[$id])) {
            $this->branch = [];
            $this->branchRecursion($id);

            return array_reverse($this->branch);
        }
    }

    private function branchRecursion($id))
    {
        $this->branch[] = $this->nodesById[$id];

        if ($this->nodesById[$id][$this->parentIdFieldName] > 0) {
            $this->branchRecursion($this->nodesById[$id][$this->parentIdFieldName]);
        }
    }
}

You need to submit a query builder as input, you can filter by some criteria (visibility, for example), sort, attach eager downloads, and so on. It is impossible to limit by the parent category, that is, it is impossible to take a subtree of a certain node in this way, this is a minus of this method.
At the output, you can get a model by its id ($tree->getNode($id)) and an array of nested model models with such and such an id ($tree->getSubnodes($id)). You can also get a branch - $tree->getBranch($id) will return an array of models from the root to the one with id = $id. Draw breadcrumbs to, for example,
a vacuum example with your table. I didn’t use Blade, but I think you can give him some kind of array or how it’s done

class Test
{
    private $tree;

    private $output;

    public function treeView()
    {
        $builder = new ProductCategory; // тут можно дописать что-нибудь типа orderBy('position') или where('enabled', true); главное так чтобы любые ограничения в первую очередь исключали вышестоящие узлы (ни в коем случае не исключали нижестоящие не исключая нижестоящие), иначе дерево получится кривое - недостаток способа

        $this->tree = (new Tree($builder))->get();

        $this->treeRecursion(0); // здесь уже можно указать ид корневого узла для дерева на выходе

        return implode('<br>', $this->output);
    }

    private $level = 0;

    private function treeViewRecursion($id)
    {
        $node = $this->tree->getNode($id);
        $subnodes = $this->tree->getSubnodes($id);

        $this->output[] = str_repeat('--', $this->level) . ' ' . $node->name;

        if ($subnodes) {
            $this->level++;

            foreach ($subnodes as $subnode) {
                $this->treeViewRecursion($subnode->id);
            }

            $this->level--;
        }
    }
}

print (new Test)->tree();

In a good way, it’s better to store the html cache of the finished tree in general and replace it after each operation on it

Answer 6

Use MPTT . Allows you to do almost all the necessary operations in 1 request: selecting a family, subtree, sons, leaves, descendants, ancestors.

Answer 7

It would be good for MySQL to confirm that the category table is created with InnoDB support. If so, then check if there is, and if not, then add the index
CREATE INDEX IX_product_categories_NC1 ON product_categories ( parent_id, name, id );

Answer 8

Thanks to all. Did as suggested by ThunderCat

Answer 9

Models/Catalog.php

public static function getCategories() {
        // Получаем одним запросом все разделы
        $arr = self::orderBy('name')->get();

        // Запускаем рекурсивную постройку дерева и отдаем на выдачу
        return self::buildTree($arr, 0);
}


// Сама функция рекурсии
public static function buildTree($arr, $pid = 0) {
        // Находим всех детей раздела
        $found = $arr->filter(function($item) use ($pid){return $item->parent_id == $pid; });

        // Каждому детю запускаем поиск его детей
        foreach ($found as $key => $cat) {
            $sub = self::buildTree($arr, $cat->id);
            $cat->sub = $sub;
            }

        return $found;
    }

All subcategories fly into the category's sub property .
We ask in the category controller:

public function index() {
        $categories = Catalog::getCategories();

        return view('Catalog.index')
            ->withCategories($categories);
    }

And in the form we display the categories recursively.
catalog.partial.show:

<ul>
    @foreach($categories as $category)
        <li>
            {{$category->name}}
            @if($category->sub->count())
                    @include('catalog.partial.show', ['categories' => $category->sub])
            @endif
        </li>
    @endforeach
</ul>