如何将原生PHP SQL查询转换为Laravel查询构造器语句

以下为和你提供的原生SQL逻辑完全等价的Laravel查询构造器写法，自带参数绑定可避免SQL注入风险：

use Illuminate\Support\Facades\DB;

$csvResult = DB::table('user_email_list')
    ->selectRaw('COUNT(email_name) as t_email, MIN(create_date) as create_time')
    ->selectRaw('(SELECT COUNT(email_name) FROM user_email_list WHERE csv_file_name = ? AND email_status = "valid" AND user_id = ?) AS count_valid', [$csv_file_name, $user_id])
    ->selectRaw('(SELECT COUNT(email_name) FROM user_email_list WHERE csv_file_name = ? AND email_status = "invalid" AND user_id = ?) AS count_invalid', [$csv_file_name, $user_id])
    ->selectRaw('(SELECT COUNT(email_name) FROM user_email_list WHERE csv_file_name = ? AND email_status = "catch all" AND user_id = ?) AS count_catchall', [$csv_file_name, $user_id])
    ->selectRaw('(SELECT COUNT(email_name) FROM user_email_list WHERE csv_file_name = ? AND email_status = "unknown" AND user_id = ?) AS count_unknown', [$csv_file_name, $user_id])
    ->selectRaw('(SELECT COUNT(email_name) FROM user_email_list WHERE csv_file_name = ? AND email_status = "Not Verify" AND user_id = ?) AS count_not_verify', [$csv_file_name, $user_id])
    ->selectRaw('(SELECT COUNT(email_name) FROM user_email_list WHERE csv_file_name = ? AND verification_response = "syntax error" AND user_id = ?) AS count_syntax', [$csv_file_name, $user_id])
    ->selectRaw('(SELECT COUNT(email_name) FROM user_email_list WHERE csv_file_name = ? AND email_type = "Free Account" AND user_id = ?) AS count_free', [$csv_file_name, $user_id])
    ->selectRaw('(SELECT COUNT(email_name) FROM user_email_list WHERE csv_file_name = ? AND email_type = "Role Account" AND user_id = ?) AS count_role', [$csv_file_name, $user_id])
    ->selectRaw('(SELECT COUNT(email_name) FROM user_email_list WHERE csv_file_name = ? AND email_type = "Disposable Account" AND user_id = ?) AS count_disposable', [$csv_file_name, $user_id])
    ->where('csv_file_name', $csv_file_name)
    ->where('user_id', $user_id)
    ->first();

优化版本（更高性能） #

你可以用条件聚合代替多子查询写法，仅需扫描一次表即可得到所有统计结果，查询效率更高，结果和原SQL完全一致：

use Illuminate\Support\Facades\DB;

$csvResult = DB::table('user_email_list')
    ->where('csv_file_name', $csv_file_name)
    ->where('user_id', $user_id)
    ->selectRaw('
        COUNT(email_name) as t_email,
        MIN(create_date) as create_time,
        SUM(CASE WHEN email_status = "valid" THEN 1 ELSE 0 END) as count_valid,
        SUM(CASE WHEN email_status = "invalid" THEN 1 ELSE 0 END) as count_invalid,
        SUM(CASE WHEN email_status = "catch all" THEN 1 ELSE 0 END) as count_catchall,
        SUM(CASE WHEN email_status = "unknown" THEN 1 ELSE 0 END) as count_unknown,
        SUM(CASE WHEN email_status = "Not Verify" THEN 1 ELSE 0 END) as count_not_verify,
        SUM(CASE WHEN verification_response = "syntax error" THEN 1 ELSE 0 END) as count_syntax,
        SUM(CASE WHEN email_type = "Free Account" THEN 1 ELSE 0 END) as count_free,
        SUM(CASE WHEN email_type = "Role Account" THEN 1 ELSE 0 END) as count_role,
        SUM(CASE WHEN email_type = "Disposable Account" THEN 1 ELSE 0 END) as count_disposable
    ')
    ->first();

用法说明 #

• 得到的$csvResult为对象格式，可直接通过$csvResult->t_email的方式读取对应字段值
• 若需要数组格式结果，在first()方法后追加->toArray()即可
• 若你已经创建了user_email_list对应的Eloquent模型，可将DB::table('user_email_list')替换为模型类名::query()使用ORM调用

内容的提问来源于stack exchange，提问作者Md Asaduzzaman