Laravel嵌套儿童计数（类别）

I using nested categories on my Laravel site with an adjacentcy list style mysql table layout. There is a maximum of 7 layers of categorization, (using google categories taxonomy). Please see the image below for a small sample of data, and the code below for my Category model's relationships.

Each category has zero to many subcategories (using same Category Model), as well as zero to many pages underneath it. My goal here is to find a way to get a count of all pages beneath a category (including pages under its subcategories).

My Category model has the following relationships which are all functioning:

public function pages() {
    return $this->hasMany('App\Models\Page');
}

public function children() {
    return $this->hasMany('App\Models\Category', 'parent_id' );
}

public function parent() {
    return $this->belongsTo('App\Models\Category', 'parent_id' );
}

public function childrenRecursive() {
   return $this->children()->orderBy( 'name' )->with('childrenRecursive', 'pages');
}

public function parentRecursive() {
   return $this->parent()->with('parentRecursive');
}

Because I am eager loading all of these categories and pages in the first palce using the following line of code and my Categories model:

$categories = Category::with( 'childrenRecursive' )->whereNull( 'parent_id' )->get();

I was able to use a recursive formula to sum up the number of pages underneath each category without slowing down the page:

foreach( $categories as $category ) {
    $category->pagesCount = 0;
    $category->pagesCount += $category->pages->count();
    foreach( $category->childrenRecursive as $child ) {
        $category->pagesCount += countChildPages( $child );
    }
}

function countChildPages( $category ) {
    $category->pagesCount = 0;
    $category->pagesCount += $category->pages->count();
    foreach( $category->childrenRecursive as $child ) {
        $category->pagesCount += countChildPages( $child );
    }
    return $category->pagesCount;
}

I then just accessed the count ($category->pagesCount) whenever I needed to use it on the page.

Example of sitemap.html page:

foreach( $categories as $category ) {
    $category->pagesCount = 0;
    $category->pagesCount += $category->pages->count();
    foreach( $category->childrenRecursive as $child ) {
        $category->pagesCount += countChildPages( $child );
    }
}

foreach( $categories as $category ) {
    if( $category->pagesCount > 0 ) {
        echo '<div class="category">';
            echo '<h2><a href="https://example.com/' . $category->slug . '">' . $category->name . ' (' . $category->pagesCount . ')</a></h2>';
            foreach( $category->pages as $page ) {
                showPage( $page );
            }
            foreach( $category->childrenRecursive as $child ) {
                showSubCategory( $child );
            }
        echo '</div>';
    }
}

function countChildPages( $category ) {
    $category->pagesCount = 0;
    $category->pagesCount += $category->pages->count();
    foreach( $category->childrenRecursive as $child ) {
        $category->pagesCount += countChildPages( $child );
    }
    return $category->pagesCount;
}

function showSubCategory( $category ) {
    if( $category->pagesCount > 0 ) {
        echo '<div class="category">';
            echo '<h2><a href="https://example.com/' . $category->slug . '">' . $category->name . ' (' . $category->pagesCount . ')</a></h2>';
            foreach( $category->pages as $page ) {
                showPage( $page );
            }
            foreach( $category->childrenRecursive as $child ) {
                showSubCategory( $child );
            }
        echo '</div>';
    }
}

function showPage( $page ) {
    echo '<div class="category page">';
        echo '<h2><a href="https://example.com/' . $page->slug . '">' . $page->title . '</a></h2>';
    echo '</div>';
}

This isn't how I would do it using relationships, but a sneaky way would be:

public function childCount(){
  return DB::table('categories')->where('slug', 'LIKE', $this->slug . '%')->count() - 1;
}

To get the pages, maybe:

public function pageCount(){
  return Page::whereIn('category_id', 
     DB::table('categories')->select('id')->where('slug', 'LIKE', $this->slug . '%')->get()->pluck('id')
  )->count();
}

2 queries which could probably be optimized, but would work