请求将指定MySQL查询转换为Laravel原生查询

Hey there! Converting that raw MySQL query to Laravel's query builder is straightforward once you map each part over. Here's the equivalent code using Laravel's native query methods:

use Illuminate\Support\Facades\DB;

// Replace $businessId with your actual variable value
$businessId = 123;

$result = DB::table('dummy_email_track as d')
    ->join('recommend_email_send_by_cron as r', 'd.user_id', '=', 'r.user_id')
    ->join('user_form_submission as u', 'r.user_id', '=', 'u.id')
    ->selectRaw('
        SUM(IF(d.business_email_template_10_open = "Yes", 1, 0)) AS business_email_template_10_open,
        SUM(IF(d.business_email_template_11_open = "Yes", 1, 0)) AS business_email_template_11_open
    ')
    ->where('d.business_id', $businessId)
    ->first();

// Access the aggregated results like this:
// $openCountTemplate10 = $result->business_email_template_10_open;
// $openCountTemplate11 = $result->business_email_template_11_open;

Quick Breakdown: #

• We start with DB::table() targeting the dummy_email_track table and alias it as d, matching your original query's structure.
• The chained join() calls replicate the exact table relationships from your raw SQL, using the correct foreign key matches.
• selectRaw() is used here to preserve the conditional SUM(IF(...)) logic—since Laravel's query builder doesn't have a built-in helper for this specific aggregation pattern, raw expressions are the cleanest way to keep your original logic intact.
• The where() clause applies the business ID filter safely, with Laravel automatically handling parameter binding to prevent SQL injection risks.
• first() retrieves the single aggregated result row, which you can access via object properties as shown in the comments.

内容的提问来源于stack exchange，提问作者Maneesh Rao