Laravel QueryBuilder subquery

Company.php

<?php
namespace App\Models;

use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Model;

class Company extends Model
{
    /**
     * Table schema is :
     *   companies [id, name]
     *   projects [id, name]
     *   batches [id, projects_id, name] (many batches per project - ManyToOne)
     *   batche_companies [id, batches_id, companies_id] (many companies in many batches - ManyToMany)
     * 
     * The goal is to retrieve number of projects per company, as array [[company_id, project_count]]
     * 
     * @return Illuminate\Support\Collection
     */
    public static function getProjectCount()
    {
        // Retrieve table names
        $companyTable = with(new static)->getTable();
        $batchCompanyTable = with(new BatchCompany)->getTable();
        $batchTable = with(new Batch)->getTable();
        $projectTable = with(new Project)->getTable();

        // The query as plain SQL
        $query = "
            select p1.company_id, COUNT(p1.project_id) as project_count
            from (
                select c.id as company_id, p.id as project_id
                from `{$companyTable}` as `c`
                inner join `{$batchCompanyTable}` as `bc` on `bc`.`company_id` = `c`.`id`
                inner join `{$batchTable}` as `b` on `b`.`id` = `bc`.`batch_id`
                inner join `{$projectTable}` as `p` on `p`.`id` = `b`.`project_id`
                where `c`.`deleted_at` is null and `b`.`deleted_at` is null and `p`.`deleted_at` is null
                group by `c`.`id`, `p`.`id`
            ) as p1
            group by `p1`.`company_id`
        ";

        return collect(DB::select(DB::raw($query)));

        // The same query, using Laravel Database QueryBuilder
        /*
        $subQuery = DB::table($companyTable, 'c')
             ->select(DB::raw('c.id as company_id, p.id as project_id'))
             ->join($batchCompanyTable. ' as bc', 'bc.company_id', '=', 'c.id')
             ->join($batchTable. ' as b', 'b.id', '=', 'bc.batch_id')
             ->join($projectTable. ' as p', 'p.id', '=', 'b.project_id')
             ->whereNull('c.deleted_at')
             ->whereNull('b.deleted_at')
             ->whereNull('p.deleted_at')
             ->groupBy('c.id')
             ->groupBy('p.id')
             ;
            
        return DB::table(DB::raw("({$subQuery->toSql()}) as p1"))
            ->select(DB::raw('p1.company_id, COUNT(p1.project_id) as project_count'))
            ->groupBy('p1.company_id')
            ->get();
        */
    }   
}