Repository: laravel/framework (php)

Issue:
[Eloquent] withCount()->having()->count() generates wrong query
<!-- DO NOT THROW THIS AWAY -->
<!-- Fill out the FULL versions with patch versions -->

- Laravel Version: 8.42.1
- PHP Version: 8.0.3
- Database Driver & Version: MariaDB

### Description:
Given two simple models
```php
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:

```php
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

```php
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

```mysql
select `users`.*, (select count(*) from `roles` where `users`.`id` = `roles`.`user_id`) as `roles_count` from `users` having `roles_count` > 2
```
```mysql
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:
```mysql
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
```

Pull request:
[8.x] fix #37483 (aggregates with having)
<!--
Please only send a pull request to branches which are currently supported: https://laravel.com/docs/releases#support-policy 

If you are unsure which branch your pull request should be sent to, please read: https://laravel.com/docs/contributions#which-branch

Pull requests without a descriptive title, thorough description, or tests will be closed.

In addition, please describe the benefit to end users; the reasons it does not break any existing features; how it makes building web applications easier, etc.
-->
### The problem

Executing the following statement results in an sql error because the subquery is dismissed but the havings are kept:

```php
User::query()->withCount('roles')->having('roles_count', '>', '2')->count()
```

Query:
```mysql
select count(*) as aggregate from `users` having `roles_count` > 2
```
Error:
```
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 executing the following runs without error
```php
User::query()->withCount('roles')->having('roles_count', '>', '2')->get()
```
Query:
```mysql
select `users`.*, (select count(*) from `roles` where `users`.`id` = `roles`.`user_id`) as `roles_count` from `users` having `roles_count` > 2
```

When comparing the queries of the previous statements, it is clear the original query should instead be wrapped within a subquery to be valid when dealing with havings to get a correct count (because having only applies to grouped data), exactly how it's currently done with unions, eg:

```mysql
select count(*) as aggregate from (select `users`.*, (select count(*) from `roles` where `users`.`id` = `roles`.`user_id`) as `roles_count` from `users` having `roles_count` > 2) as `temp_table`
```

## This PR
It applies the methodology of aggregates with union to aggregates with havings and does indeed fix the aforementioned issue #37483

It is not a breaking change because the previous behavior of this specific case wasn't previously working

## TLDR

Queries with havings should behave exactly the same way as unions when executing aggregates functions


Patch:
diff --git a/src/Illuminate/Database/Query/Builder.php b/src/Illuminate/Database/Query/Builder.php
index 9df7d46eec..0ef10ac1c2 100755
--- a/src/Illuminate/Database/Query/Builder.php
+++ b/src/Illuminate/Database/Query/Builder.php
@@ -2826,8 +2826,8 @@ public function average($column)
      */
     public function aggregate($function, $columns = ['*'])
     {
-        $results = $this->cloneWithout($this->unions ? [] : ['columns'])
-                        ->cloneWithoutBindings($this->unions ? [] : ['select'])
+        $results = $this->cloneWithout($this->unions || $this->havings ? [] : ['columns'])
+                        ->cloneWithoutBindings($this->unions || $this->havings ? [] : ['select'])
                         ->setAggregate($function, $columns)
                         ->get($columns);
 
diff --git a/src/Illuminate/Database/Query/Grammars/Grammar.php b/src/Illuminate/Database/Query/Grammars/Grammar.php
index b7305e8ea3..d7bc534da1 100755
--- a/src/Illuminate/Database/Query/Grammars/Grammar.php
+++ b/src/Illuminate/Database/Query/Grammars/Grammar.php
@@ -45,7 +45,7 @@ class Grammar extends BaseGrammar
      */
     public function compileSelect(Builder $query)
     {
-        if ($query->unions && $query->aggregate) {
+        if (($query->unions || $query->havings) && $query->aggregate) {
             return $this->compileUnionAggregate($query);
         }
 


Test patch:
diff --git a/tests/Database/DatabaseQueryBuilderTest.php b/tests/Database/DatabaseQueryBuilderTest.php
index 5adc3a6d9b..7e5adbad4c 100755
--- a/tests/Database/DatabaseQueryBuilderTest.php
+++ b/tests/Database/DatabaseQueryBuilderTest.php
@@ -1034,6 +1034,22 @@ public function testUnionAggregate()
         $builder->from('posts')->union($this->getSqlServerBuilder()->from('videos'))->count();
     }
 
+    public function testHavingAggregate()
+    {
+        $expected = 'select count(*) as aggregate from (select (select `count(*)` from `videos` where `posts`.`id` = `videos`.`post_id`) as `videos_count` from `posts` having `videos_count` > ?) as `temp_table`';
+        $builder = $this->getMySqlBuilder();
+        $builder->getConnection()->shouldReceive('getDatabaseName');
+        $builder->getConnection()->shouldReceive('select')->once()->with($expected, [0 => 1], true)->andReturn([['aggregate' => 1]]);
+        $builder->getProcessor()->shouldReceive('processSelect')->once()->andReturnUsing(function ($builder, $results) {
+            return $results;
+        });
+
+        $builder->from('posts')->selectSub(function ($query) {
+            $query->from('videos')->select('count(*)')->whereColumn('posts.id', '=', 'videos.post_id');
+        }, 'videos_count')->having('videos_count', '>', 1);
+        $builder->count();
+    }
+
     public function testSubSelectWhereIns()
     {
         $builder = $this->getBuilder();