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}
