Laravel：如何统计嵌套预加载关联中的线程及帖子数量？

Hey there! Glad to hear your model associations are already working correctly—let's nail those count stats without pulling all the related objects (way more efficient this way, too!).

The core idea here is to leverage database-level aggregation instead of loading full collections and counting in memory. Below are tailored solutions for different scenarios:

1. 统计单个CategoryBoard的threads和threadPosts数量 #

If you're working with a single CategoryBoard instance, use the association's built-in count() method for threads, and either a nested count or direct join for total posts:

// Example using Laravel Eloquent (adjust syntax for your ORM as needed)
$categoryBoard = CategoryBoard::find($boardId);

// Count threads for this board (database-level count)
$threadCount = $categoryBoard->threads()->count();

// Count total threadPosts across all threads in this board (efficient join approach)
$postCount = DB::table('threads')
    ->join('thread_posts', 'threads.id', '=', 'thread_posts.thread_id')
    ->where('threads.category_board_id', $categoryBoard->id)
    ->count();

2. 批量统计所有CategoryBoards的threads和posts #

To get stats for all CategoryBoards in one go, use withCount() for threads and a subquery to calculate total posts:

$categoryBoards = CategoryBoard::withCount('threads')
    ->addSelect([
        'total_posts' => Thread::selectRaw('count(*)')
            ->whereColumn('category_board_id', 'category_boards.id')
            ->join('thread_posts', 'threads.id', '=', 'thread_posts.thread_id')
    ])
    ->get();

// Access stats directly on each board instance
foreach ($categoryBoards as $board) {
    echo "Board: {$board->name} | Threads: {$board->threads_count} | Total Posts: {$board->total_posts}";
}

3. 统计单个Category的总threads和posts #

Since a Category has many CategoryBoards, we can aggregate stats across its nested associations:

$category = Category::find($categoryId);

// Total threads across all boards in the category
$totalThreads = $category->categoryBoards()->withCount('threads')->get()->sum('threads_count');

// Total posts across all threads in the category (direct database join)
$totalPosts = DB::table('categories')
    ->join('category_boards', 'categories.id', '=', 'category_boards.category_id')
    ->join('threads', 'category_boards.id', '=', 'threads.category_board_id')
    ->join('thread_posts', 'threads.id', '=', 'thread_posts.thread_id')
    ->where('categories.id', $categoryId)
    ->count();

Pro Tips #

• Always prefer $model->relation()->count() over $model->relation->count(): The former runs a COUNT() query directly in the database, while the latter loads every related object into memory first—this is a massive performance difference for large datasets.
• Use joins or subqueries for nested counts to minimize database round-trips.

内容的提问来源于stack exchange，提问作者Jonathan Lightbringer