Eager loading does not work by nested relationships using withCount and loadCount

[blablabla1234678]

Laravel Version

11.21.0

PHP Version

8.2.0

Database Driver & Version

MySQL 8.0.31

Description

I have factories, categories and products in my database. Each factory has multiple categories and each category has multiple products. These are 1:n relationships. So for example I use return $this->hasMany(Product::class); in the Category class. In my query I load all categories for all factories and I want to eager load the product count for all categories, but not the products. I do it this way:

    $factories = Factory::all();
    $factories->load(['categories' => function ($category){
        $category->withCount('products');
    }]);

Normally this can be done with a single SQL query, but according to the logs it takes one SQL query for each category instead...

In my blade template I get the product count this way:

    {{$category->name}} ({{$category->products()->count()}})

This appears to be a bug for me, at least I tried it with both with and load with the same result.

Steps To Reproduce

migration:

Schema::create('factories', function (Blueprint $table){
    $table->id();
    $table->string('name');
    $table->timestamps();
});
Schema::create('categories', function (Blueprint $table){
    $table->id();
    $table->foreignId('factory_id')->constrained('factories', 'id')->onUpdate('cascade')->onDelete('cascade');
    $table->string('name');
    $table->timestamps();
});
Schema::create('products', function (Blueprint $table) {
    $table->id();
    $table->foreignId('category_id')->constrained('categories', 'id')->onUpdate('cascade')->onDelete('cascade');
    $table->string('name');
    $table->timestamps();
});

models

class Factory extends Model
{
    use HasFactory;

    public function categories(){
        return $this->hasMany(Category::class);
    }
}

class Category extends Model
{
    use HasFactory;

    public function products(){
        return $this->hasMany(Product::class);
    }
}

controller

$factories = Factory::all();
$factories->load('translation');
$factories->load(['categories' => function ($category){
    $category->withCount('products');
}]);
return View::make('products.index', [
    'factories' => $factories
]);

view:

@foreach ($factories as $factory)
{{$factory->name}}<br>
@foreach ($factory->categories as $category)
{{$category->name}} ({{$category->products()->count()}})<br>
@endforeach
@endforeach

AppServiceProvider

public function boot(): void
{
    if (App::environment('local'))
        DB::listen(function($query) {
            Log::info(
                $query->sql,
                [
                    'bindings' => $query->bindings,
                    'time' => $query->time
                ]
            );
        });
}

If you check the log, then there will be multiple queries in it something like

[2024-09-13 14:26:52] local.INFO: select count(*) as aggregate from products where products.category_id = ? and products.category_id is not null {"bindings":[13],"time":0.69}

[github-actions]

Thank you for reporting this issue!

As Laravel is an open source project, we rely on the community to help us diagnose and fix issues as it is not possible to research and fix every issue reported to us via GitHub.

If possible, please make a pull request fixing the issue you have described, along with corresponding tests. All pull requests are promptly reviewed by the Laravel team.

Thank you!

[blablabla1234678]

I have an even weirder behavior. I have translation tables in the original code for multi language site. When I fix the upper counting problem with

    public function productsCount(){
        return $this->products()
            ->selectRaw('category_id, COUNT(id) as count')
            ->groupBy('category_id');
    }

and

        $factories = Factory::all();
        $factories->load('translation');
        $factories->load('categories');
        $factories->load('categories.translation');
        $factories->load('categories.productsCount');

and

{{$category->productsCount->value('count')}}

Then the categories.translation starts to send a query for each categories which was previously completely okay. So it pretty much looks like this is a bug.

[blablabla1234678]

Can somebody confirm that the first code reproduces the bug? If not, then I'll check, maybe it needs more queries and tables, but it is there.

[blablabla1234678]

Turned out this is not a bug. The view needs {{$category->products_count}} instead of {{$category->products()->count()}}.