[Eloquent] withCount()->having()->count() generates wrong query

[Tofandel]

• Laravel Version: 8.42.1
• PHP Version: 8.0.3
• Database Driver & Version: MariaDB

Description:

Given two simple models

Illuminate\Support\Facades\Schema::create('users', function (Blueprint $table) {
  $table->id();
});
Illuminate\Support\Facades\Schema::create('roles', function (Blueprint $table) {
  $table->id();
 $table->foreignId('user_id')
                ->constrained()
                ->cascadeOnDelete()->cascadeOnUpdate();
});
class Role extends Model {
    public function user() {
        return $this->belongsTo( User::class );
    }
}

class User extends Model {
     public function codes() {
        return $this->hasMany( Role::class );
    }
}

Running the following statement results in an sql error:

User::query()->withCount('roles')->having('roles_count', '>', '2')->count()

Illuminate\Database\QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'roles_count' in 'having clause' (SQL: select count(*) as aggregate from `users` having `roles_count` > 2)'

But the following runs without error

User::query()->withCount('roles')->having('roles_count', '>', '2')->get()

The count method should be interchangeable and work with any query that get works with but currently it doesn't seem to generate a correct query when there is an having in the original query

When comparing both queries

select `users`.*, (select count(*) from `roles` where `users`.`id` = `roles`.`user_id`) as `roles_count` from `users` having `roles_count` > 2

select count(*) as aggregate from `users` having `roles_count` > 2

The query should instead be wrapped within another select to be valid when dealing with havings, eg:

select count(*) as aggregate from (select users.*, (select count(*) from `roles` where `users`.`id` = `codes`.`user_id`) as `roles_count` from `users` having `roles_count` > 2) as sel

[driesvints]

As you can see from your changes in the PR that'll break the test suite. So I don't think the above is wanted. If you really think there's a bug feel free to try a new PR with passing tests. Thanks

[Tofandel]

Sure that's what I'm doing, but why close the issue?