如何在Laravel 8中转换含双统计计数的SQL查询且不使用switch case

以下是将你提供的SQL转换为Laravel 8查询构造器的代码，完全匹配原SQL的关联逻辑、字段选择和统计需求：

use Illuminate\Support\Facades\DB;

$results = DB::table('compliance_visits')
    ->join('facilities', 'compliance_visits.facility_id', '=', 'facilities.facility_id')
    ->join('beneficiaries', 'compliance_visits.beneficiary_id', '=', 'beneficiaries.beneficiary_id')
    ->join('districts', 'facilities.district_id', '=', 'districts.district_id')
    ->join('talukas', 'facilities.taluka_id', '=', 'talukas.taluka_id')
    ->select(
        'districts.district_name as District',
        'facilities.facility_name as Name_Of_Health_Facility',
        DB::raw('COUNT(beneficiaries.name) as No_Of_Registered_Client'),
        DB::raw('COUNT(compliance_visits.facility_id) as Compliance_visitors')
    )
    ->groupBy('facilities.facility_id')
    ->get();

关键对应说明： #

• 用DB::table()指定主表compliance_visits，完全对应原SQL的FROM子句
• 多次调用join()实现所有INNER JOIN关联，关联条件与原SQL完全一致
• 通过select()选择所需字段，统计计数用DB::raw()直接映射原SQL的COUNT逻辑，无需switch case
• 用groupBy()按facilities.facility_id分组，最后get()执行查询并返回结果集

内容的提问来源于stack exchange，提问作者Ali Arsalan