Different values from counting

[k0mar12]

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.

[crynobone]

Hey there, thanks for reporting this issue.

We'll need more info and/or code to debug this further. Can you please create a repository with the command below, commit the code that reproduces the issue as one separate commit on the main/master branch and share the repository here?

Please make sure that you have the latest version of the Laravel installer in order to run this command. Please also make sure you have both Git & the GitHub CLI tool properly set up.

laravel new bug-report --github="--public"

Do not amend and create a separate commit with your custom changes. After you've posted the repository, we'll try to reproduce the issue.

Thanks!

[k0mar12]

@crynobone GDay

Branch: issue

Install

php artisan migrate
php artisan db:seed

Presentation

I have presented in /route/web.php

[driesvints]

Hi there @k0mar12. This seems more like an issue for a support channel. You're doing quite some complex query building here and right now we don't have time to dig into this. Please try a support channel to see if anyone can help you out. If you're really sure there's a bug in Laravel we'd appreciate a PR with a failing test, thanks.

• Laracasts Forums
• Laravel.io Forums
• StackOverflow
• Discord
• Larachat
• IRC