Laravel eloquent get relation count

Laravel eloquent get relation count

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Laravel Eloquent Get Relation Count for Parent Categories

When building admin dashboards or public lists, displaying hierarchical data with aggregated metrics is a common requirement. You often need to show parent categories alongside the count of their direct children (articles), rather than listing every single child row. In your specific case, you are working with a categories table that has self-referential relationships and an articles table linked by a cat_id.

The core challenge lies in filtering for only the root-level categories (parent_id = 0) while simultaneously aggregating counts from the child levels without duplicating rows or running inefficient subqueries. Your initial attempt using leftJoin mixed with a closure subquery is on the right track conceptually, but it hits a common snag: Eloquent's query builder struggles when mixing specific table references inside closures with complex joins that need to filter based on hierarchy depth.

Using a Direct SQL Aggregation

The most robust solution in Laravel 5.3 for this scenario is often to bypass the relationship aggregation entirely and write a targeted raw query or use a clean DB facade call. This approach gives you explicit control over the join logic and ensures you are only selecting parent categories.

Here is how you can construct the query to get a list of root categories with their article counts:

use Illuminate\Support\Facades\DB;

$rootCategories = DB::table('categories')
    ->leftJoin('articles', 'articles.cat_id', '=', 'categories.id')
    ->where('categories.parent_id', 0) // Filter only for root parents
    ->select(
        'categories.id',
        'categories.title',
        DB::raw('COUNT(articles.id) as article_count')
    )
    ->groupBy('categories.id', 'categories.title')
    ->get();

This query performs an inner join implicitly on the count because COUNT(articles.id) ignores nulls, effectively acting like a left join for the purpose of counting. If a category has no articles, it simply won't appear in the result set unless you change the strategy to LEFT JOIN and handle the null counts in your view logic.

Handling Null Counts with Left Joins

If your requirement is to display the parent category even when it contains zero articles, you must use an explicit LEFT JOIN. Since Laravel 5.3 does not allow mixing raw SQL keywords like LEFT JOIN directly inside a select closure easily without potential ambiguity, sticking to the DB::table() facade (as shown above) is the cleanest path.

If you prefer using Eloquent models strictly, you can leverage the relationship but must be careful about how you filter. A common pattern involves querying the parent categories first and then eager loading the count via a custom accessor or simply running a query on the relation:

$rootCategories = Category::where('parent_id', 0)->get();

foreach ($rootCategories as $category) {
    // This counts only direct children linked to this specific category ID
    $count = DB::table('articles')
        ->where('cat_id', $category->id)
        ->count();
    
    // Or use the eager loading approach if you need the model instance
    $category->load(['children' => function ($q) {
        $q->selectRaw('count(*) as total'); 
        // Note: Eloquent doesn't support direct count() on loaded relations 
        // in a single query easily without raw SQL tricks.
    }]);
}

However, the raw aggregation approach via DB::table is significantly more performant because it executes a single database round-trip rather than iterating through models and running individual counts.

Why Your Subquery Approach Struggled

In your code snippet, you attempted to use whereIn('article.cat_id', ...) with a closure that queries the categories table again. The issue arises when trying to reference $this->tableName or mixing specific join conditions inside the closure while filtering by parent_id. Eloquent's query builder expects the subquery to return a simple list of IDs, but constructing that logic dynamically often leads to SQL syntax errors regarding column references or ambiguous joins.

By shifting the responsibility to the DB facade for this specific aggregation step, you decouple the relationship definition from the counting logic. This allows you to explicitly state: "Give me rows from categories where parent_id is 0, and count matching articles."

Optimization Considerations

If you have thousands of categories, ensure your indexes are optimized. The cat_id column in the articles table must be indexed for the join to be efficient. Additionally, if this query runs frequently, consider caching the result set or pre-calculating counts into a separate summary table if the data changes infrequently.

For more complex hierarchical queries or when dealing with deep nesting, Laravel's Eloquent relationships provide powerful tools like withCount, but they typically aggregate based on direct relations. When you need to filter by hierarchy depth (like parent_id = 0), raw SQL aggregation remains the most predictable and maintainable solution in Laravel 5.3.

Our network