Closed
Description
Laravel Version
v10.5.0
PHP Version
8.2.4
Database Driver & Version
MySQL 8.0.29
Description
I have next models:
Round
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\HasMany;
class Round extends Model
{
protected $fillable = ['phase', 'type', 'lobby_mode', 'composition', 'index'];
public function sides(): HasMany
{
return $this->hasMany(Side::class);
}
public function mainSides(): HasMany
{
return $this->sides()->mainTypes();
}
public function filledMainSides(): HasMany
{
return $this->mainSides()->filled();
}
}
Side
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Relations\BelongsTo;
use Illuminate\Database\Eloquent\Relations\MorphMany;
use Illuminate\Contracts\Database\Query\Builder;
class Side extends Model
{
protected $fillable = ['type'];
public function round(): BelongsTo
{
return $this->belongsTo(Round::class);
}
public function participants(): MorphMany
{
return $this->morphMany(Participant::class, 'participial');
}
public function scopeFilled(Builder $builder): Builder
{
return $builder
->withCount('participants as joined_members')
->withAggregate('round', 'composition')
->havingRaw('`joined_members` = `round_composition`');
}
public function scopeMainTypes(Builder $builder): Builder
{
return $builder->where('type', 0)->orWhere('type', 1);
}
}
Given
In total round has 2 main sides, Dire and Radiant, but filled only one. Filled side it is side which has a completed list of users.
Case 1
Description
I try to fetch Round with filled sides - i have correct result.
Dump
SQL
select * from `rounds` where `id` = 109 limit 1
select
`sides`.*,
(
select
count(*)
from
`participants`
where
`sides`.`id` = `participants`.`participial_id`
and `participants`.`participial_type` = 'Modules\Game\Models\Side'
) as `joined_members`,
(
select
composition
from
`rounds`
where
`sides`.`round_id` = `rounds`.`id`
limit
1
) as `round_composition`
from
`sides`
where
(
`type` = 0
or `type` = 1
)
and `sides`.`round_id` in (109)
having
`joined_members` = `round_composition`
Case 2
Description
I try to count filled sides in fetched Round - i have correct result.
Code
$count = Round::find(109)->filledMainSides()->count();
Dump
SQL
select * from `rounds` where `rounds`.`id` = 109 limit 1
select
count(*) as aggregate
from
(
select
`sides`.*,
(
select
count(*)
from
`participants`
where
`sides`.`id` = `participants`.`participial_id`
and `participants`.`participial_type` = 'Modules\Game\Models\Side'
) as `joined_members`,
(
select
composition
from
`rounds`
where
`sides`.`round_id` = `rounds`.`id`
limit
1
) as `round_composition`
from
`sides`
where
`sides`.`round_id` = 109
and `sides`.`round_id` is not null
and (
`type` = 0
or `type` = 1
)
having
`joined_members` = `round_composition`
) as `temp_table`
Case 3
Description
But, if i try to fetch Round with withCount of filled main sides, i will get count of all available sides.
Code
$round = Round::whereId(109)->with('filledMainSides')->withCount('filledMainSides')->first();
Dump
SQL
select
`rounds`.*,
(
select
count(*)
from
`sides`
where
`rounds`.`id` = `sides`.`round_id`
and (
`type` = 0
or `type` = 1
)
) as `filled_main_sides_count`
from
`rounds`
where
`id` = 109
limit
1
select
`sides`.*,
(
select
count(*)
from
`participants`
where
`sides`.`id` = `participants`.`participial_id`
and `participants`.`participial_type` = 'Modules\Game\Models\Side'
) as `joined_members`,
(
select
composition
from
`rounds`
where
`sides`.`round_id` = `rounds`.`id`
limit
1
) as `round_composition`
from
`sides`
where
(
`type` = 0
or `type` = 1
)
and `sides`.`round_id` in (109)
having
`joined_members` = `round_composition`
Steps To Reproduce
To a greater extent, described in previous section. But if it needed, i can create demo.