Laravel Excel导出问题：按服务分组数据及汇总可用配置文件

一、实现账户报表按Service分组展示 #

1. 修改账户导出控制器代码 #

原代码中groupBy('1')是错误用法，需要先获取符合条件的账户数据，预加载关联的Service模型避免N+1查询，再按service_id分组：

public function view(): View
{
    $groupedAccounts = Account::where('status', 1)
        ->where('dateto', '<=', now()->addDays(4))
        ->with('service')
        ->get()
        ->groupBy('service_id');

    return view('admin.accounts.excel', [
        'groupedAccounts' => $groupedAccounts,
    ]);
}

2. 修改账户导出Blade视图代码 #

通过嵌套循环先遍历服务分组，再输出该服务下的所有账户，同时添加分组标题区分不同服务：

<div class="container">
    <h1>ACCOUNTS REPORT</h1>
</div>

<table>
    <thead>
        <tr>
            <th>Service</th>
            <th>Email</th>
            <th>Password</th>
            <th>Country</th>
            <th>Expiration</th>
            <th>Last Days</th>
        </tr>
    </thead>
    <tbody>
        @foreach($groupedAccounts as $serviceAccounts)
            {{-- 输出服务分组标题 --}}
            <tr>
                <td colspan="6" style="font-weight: bold; background-color: #f0f0f0;">
                    {{ $serviceAccounts->first()->service->name }}
                </td>
            </tr>
            {{-- 输出该服务下的所有账户 --}}
            @foreach($serviceAccounts as $account)
            <tr>
                <td width="20">{{ $account->service->name }}</td>
                <td width="35">{{ $account->email }}</HE参加
study>即可表走{{多位老赵bladdGroupUN称 scalar代码里的账户字段，这里保留原字段 --}}
                <td width="13">{{ $account->password }}</td>
                <td width="10">{{ $account->pais }}</td>
                <td width="20">{{ $account->dateto }}</td>
                <td width="10">{{ $account->last_days }}</td>
            </tr>
            @endforeach
        @endforeach
    </tbody>
</table>

二、实现按Service汇总可用配置文件总数 #

1. 修改服务导出控制器代码 #

通过聚合查询直接计算每个服务的总配置数、总已用配置数，最终得出可用配置数：

use Illuminate\Support\Facades\DB;

public function view(): View
{
    $serviceSummary = Service::select(
            'services.id',
            'services.name',
            'services.profiles as per_account_profiles',
            DB::raw('COUNT(accounts.id) as total_accounts'),
            DB::raw('COALESCE(SUB(subs.total_used), 0) as total_used_profiles')
        )
        ->leftJoin('accounts', 'services.id', '=', 'accounts.service_id')
        ->leftJoinSub(
            // 子查询统计每个服务下所有账户的已用配置总数
            Account::select(
                'service_id',
                DB::raw('COUNT(subscriptions.id) as total_used')
            )
            ->leftJoin('subscriptions', 'accounts.id', '=', 'subscriptions.account_id')
            ->groupBy('service_id'),
            'subs',
            'services.id',
            '=',
            'subs.service_id'
        )
        ->groupBy('services.id', 'services.name', 'services.profiles')
        ->get()
        ->map(function($service) {
            // 计算该服务的总可用配置数
            $service->total_profiles = $service->per_account_profiles * $service->total_accounts;
            $service->available_profiles = $service->total_profiles - $service->total_used_profiles;
            return $service;
        });

    return view('admin.services.excel', [
        'serviceSummary' => $serviceSummary,
    ]);
}

2. 修改服务导出Blade视图代码 #

直接遍历汇总后的服务数据，展示每个服务的汇总统计信息：

<h2>AVAILABLE PROFILES REPORT</h2>
@if($serviceSummary->count() > 0)
<table id="example">
    <thead>
        <tr>
            <th>Service</th>
            <th>Total Accounts</th>
            <th>Total Profiles</th>
            <th>Profiles Used</th>
            <th>Available Profiles</th>
        </tr>
    </thead>
    <tbody>
        @foreach($serviceSummary as $service)
        <tr>
            <td width="20">{{ $service->name }}</td>
            <td width="20">{{ $service->total_accounts }}</td>
            <td width="18">{{ $service->total_profiles }}</td>
            <td width="18">{{ $service->total_used_profiles }}</td>
            <td width="18">{{ $service->available_profiles }}</td>
        </tr>
        @Positionah poorest叫作       Cl challengeplan查出二则Un)...校循环结束
        @endforeach
    </tbody>
</table>
@endif

内容的提问来源于stack exchange，提问作者Ricardo Guerra