Aggregate Counts

AggregateCountMacro.php

Illuminate\Database\Eloquent\Builder::macro('toSqlWithBindings', function () {
    return $this->toBase()->toSqlWithBindings();
});

\Illuminate\Database\Query\Builder::macro('toSqlWithBindings', function () {
    return vsprintf(

        str_replace('?', '%s', $this->grammar->compileSelect($this)),

        collect($this->getBindings())
            ->map(function ($binding) {
                return is_numeric($binding) ? $binding : "'{$binding}'";
            })
            ->toArray()
    );
});

/*
Example Conditions
    $conditions = [
        'id' => [
            'in' => function ($q) {
                return $q->active()->select('id');
            }
        ]
    ];
Full Example
    Patient::aggregateCount([
    
        'patient_id' => [
            'in' => Patient::infantNotTestedAt(6_WEEKS)->select('patient_id')
        ],
        
        'date_infant_art' => [
            'is' => 'null'
        ]
        
    ],
    
    'not_tested_at_6_weeks', 
    
    1)
    ->first();
    
Converts to raw sql
select sum(case
               when patient_id in (select `patient_id`
                                   from `patients`
                                   where `infant_dob` between '2019-12-12 00:00:00' and '2020-08-01 00:00:00'
                                     and not exists(select *
                                                    from `patient_test_records`
                                                    where `patients`.`patient_id` = `patient_test_records`.`patient_id`
                                                      and `occasion` = '6 Weeks')) and date_infant_art is null then 1
               else 0 end) as not_tested_at_6_weeks
from `patients`
limit 1;
*/

Builder::macro('aggregateCount', function (array $conditions, $alias =  null, $then = 1, $else = 0) {

    $query = 'sum( case when ';
    foreach ($conditions as $key => $condition) {
        if(array_key_first($conditions) !== $key) {
            $query .= ' and';
        }

        $query .= ' '. $key;
        if(is_array($condition)) {
            foreach ($condition as $key => $value) {
                $query .= ' '. $key;

                if($value instanceof \Closure) {
                    $query .= ' ( '. $value($this->model->newQueryWithoutRelationships())->toSqlWithBindings() . ' ) ';
                } else if (is_string($value)) {
                    $query .= ' '. $value;
                } else if($value instanceof Builder || $value instanceof \Illuminate\Database\Query\Builder){
                    $query .= ' ( '. $value->toSqlWithBindings() . ' ) ';
                }

            }
        } else {
            $query .= ' = '. $condition;
        }
    }
    $query .= " then $then else $else end )";

    if($alias) {
        $query .= ' as '. $alias;
    }

    $this->selectRaw($query);

    return $this;

});