如何在Laravel中编写实现产前护理阶段分组统计的SQL查询语句

Got it, let's convert that raw SQL into clean, maintainable Laravel code. I'll show you a couple of approaches—including an optimized version to streamline the count logic—plus how to use Eloquent if you have models set up.

Basic Query Builder Implementation #

This directly mirrors your original SQL, using DB::raw() for the conditional counts since Laravel's query builder doesn't have a native method for that exact CASE WHEN + COUNT pattern:

use Illuminate\Support\Facades\DB;

$facilityAncStats = DB::table('compliance_visits')
    // Join the facilities table on matching IDs
    ->join('facilities', 'compliance_visits.facility_id', '=', 'facilities.facility_id')
    // Join the compliances_master table on matching IDs
    ->join('compliances_master', 'compliance_visits.compliance_id', '=', 'compliances_master.compliance_id')
    // Select facility name and conditional count columns
    ->select([
        'facilities.facility_name',
        DB::raw("CASE WHEN compliances_master.compliance_phase_name = 'Antenatal Care-1' THEN COUNT(compliances_master.compliance_phase_name = 'Antenatal Care-1') ELSE 0 END AS ANS_1"),
        DB::raw("CASE WHEN compliances_master.compliance_phase_name = 'Antenatal Care-2' THEN COUNT(compliances_master.compliance_phase_name = 'Antenatal Care-2') ELSE 0 END AS ANS_2"),
        DB::raw("CASE WHEN compliances_master.compliance_phase_name = 'Antenatal Care-3' THEN COUNT(compliances_master.compliance_phase_name = 'Antenatal Care-3') ELSE 0 END AS ANS_3"),
        DB::raw("CASE WHEN compliances_master.compliance_phase_name = 'Antenatal Care-4' THEN COUNT(compliances_master.compliance_phase_name = 'Antenatal Care-4') ELSE 0 END AS ANS_4"),
        DB::raw("COUNT(compliances_master.compliance_phase_name) AS ANC_total")
    ])
    // Group results by facility name to aggregate counts per facility
    ->groupBy('facilities.facility_name')
    // Execute the query and fetch results
    ->get();

Optimized Version (Cleaner & More Efficient) #

Your original CASE WHEN + COUNT logic can be simplified using SUM() instead. In MySQL, boolean expressions evaluate to 1 (true) or 0 (false), so summing the condition directly gives you the count of matching records. This is more concise and often performs better:

use Illuminate\Support\Facades\DB;

$facilityAncStats = DB::table('compliance_visits')
    ->join('facilities', 'compliance_visits.facility_id', '=', 'facilities.facility_id')
    ->join('compliances_master', 'compliance_visits.compliance_id', '=', 'compliances_master.compliance_id')
    ->select([
        'facilities.facility_name',
        DB::raw("SUM(compliances_master.compliance_phase_name = 'Antenatal Care-1') AS ANS_1"),
        DB::raw("SUM(compliances_master.compliance_phase_name = 'Antenatal Care-2') AS ANS_2"),
        DB::raw("SUM(compliances_master.compliance_phase_name = 'Antenatal Care-3') AS ANS_3"),
        DB::raw("SUM(compliances_master.compliance_phase_name = 'Antenatal Care-4') AS ANS_4"),
        DB::raw("COUNT(compliances_master.compliance_phase_name) AS ANC_total")
    ])
    ->groupBy('facilities.facility_name')
    ->get();

Using Eloquent (If You Have Models) #

If you've set up Eloquent models for your tables (e.g., ComplianceVisit, Facility, ComplianceMaster), you can write this using Eloquent's query builder to stay aligned with Laravel's conventions:

use App\Models\ComplianceVisit;

$facilityAncStats = ComplianceVisit::query()
    ->join('facilities', 'compliance_visits.facility_id', '=', 'facilities.facility_id')
    ->join('compliances_master', 'compliance_visits.compliance_id', '=', 'compliances_master.compliance_id')
    ->select([
        'facilities.facility_name',
        DB::raw("SUM(compliances_master.compliance_phase_name = 'Antenatal Care-1') AS ANS_1"),
        DB::raw("SUM(compliances_master.compliance_phase_name = 'Antenatal Care-2') AS ANS_2"),
        DB::raw("SUM(compliances_master.compliance_phase_name = 'Antenatal Care-3') AS ANS_3"),
        DB::raw("SUM(compliances_master.compliance_phase_name = 'Antenatal Care-4') AS ANS_4"),
        DB::raw("COUNT(compliances_master.compliance_phase_name) AS ANC_total")
    ])
    ->groupBy('facilities.facility_name')
    ->get();

All three versions will produce identical results to your original SQL—the optimized approach is just a cleaner way to handle the conditional counts.

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