Laravel Loop Model

HOHOOH 0
Viewed 35

I'm having some concerns with looping a Laravel model. I have 3 tables that are associated with one another, and one of them should be looped with children and show relations of the other tables as well, so what is the deal here..

I have 3 tables:

  1. categories

    • id
    • parent_id = relation to the same table foreign id
    • name = string

  2. threads

    • id
    • category_id = relation with categories
    • title
    • desc

  3. posts

    • id
    • category_id = to make query easier
    • thread_id = relation with threads
    • description

So, I want to achieve looping threw the categories model, get all models and show the json. So what am I doing.

I have in controller like:

Category::whereNull('parent_id')->with(['loopInfinite' => function ($query) {
            $query->withCount('posts', 'threads');
        }])->withCount('posts', 'threads')->orderBy('order')->get();

So I can get directly from a parent. And counting the loopInfinite

And loopInfinite looks like:

public function loopInfinite(): HasMany
    {
        return $this->hasMany(static::class, 'parent_id')->with('loopInfinite')->withCount('posts', 'threads');
    }

So here, I'm getting infinite loop that works fine. But, the count will work only for the specific category, and I want that any parent will include a count of the children, but to show only first children of the parent. Any ideas how to do it? And I'm calling withCount to many times, I believe it's not the best practise in that case.

php laravel
1 Answers

Let's optimize that

For the first with an infinity loop, you will have a lot of SQL queries to fetch all categories and sub-queries for calculating counts. As for the result, you will have a tree of categories with counts of posts/threads.

My suggestion is this: Make a single query to get all categories and calculate counts. Then using the loop generates a tree of categories and calculate the count of posts and threads for parent categories.

As a result, we will have an SQL query with two sub-queries. It will be very performed.

Category::withCount('posts', 'threads')->orderBy('order')->get()

For the generation tree, you can use this Example

Then or maybe while generation the tree you can map whole categories and sum counts of children's posts and threads for calculation the counts for parent category

userlY2z0
Related