代码之家 › 专栏 › 技术社区 › LucyTurtle

Laravel嵌套子项计数(类别)

adjacency-list eloquent laravel php

LucyTurtle · 技术社区 · 7 年前

每个类别都有零到多个子类别(使用相同的类别模型),以及它下面的零到多个页面。 我的目标是找到一种方法来统计一个类别下的所有页面(包括其子类别下的页面)。

我的类别模型具有以下关系,这些关系都起作用:

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');
}

2 回复 | 直到 7 年前

Jeff 7 年前

这不是我利用人际关系的方式,但一种偷偷摸摸的方式是:

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

要获取页面,可能:

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

2个查询,可能可以优化,但可以工作

LucyTurtle 7 年前

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

我能够使用递归公式来计算每个类别下的页数,而不会减慢页面速度:

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;
}

例子 sitemap.html页面的名称:

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>';
}