如何将Laravel查询构造器的Join关联查询改写为子查询

原有Join查询代码 #

$jobId = 12;
$customerId = 15;
DB::table('user_access_customer_job')
                ->join('user_access_customer_job as parent_jobs', 'user_access_customer_job.parent_job_id', '=', 'parent_jobs.job_id')
                ->where('parent_jobs.job_id', '<>', $jobId)
                ->where('parent_jobs.customer_id', $customerId)
                ->where('user_access_customer_job.job_id', '<>', $jobId)
                ->whereRaw('parent_jobs.user_id <=> user_access_customer_job.user_id')
                ->whereRaw('parent_jobs.type = user_access_customer_job.type')
                ->select('parent_jobs.id')
                ->lists('id');

等价子查询改写结果 #

改写使用whereExists子查询替代自Join，和原逻辑100%等价，同时避免Join可能产生的重复数据问题，执行效率更优：

$jobId = 12;
$customerId = 15;
DB::table('user_access_customer_job as parent_jobs')
    ->where('parent_jobs.job_id', '<>', $jobId)
    ->where('parent_jobs.customer_id', $customerId)
    ->whereExists(function ($query) use ($jobId) {
        $query->select(DB::raw(1))
            ->from('user_access_customer_job')
            ->whereColumn('user_access_customer_job.parent_job_id', 'parent_jobs.job_id')
            ->where('user_access_customer_job.job_id', '<>', $jobId)
            ->whereRaw('user_access_customer_job.user_id <=> parent_jobs.user_id')
            ->whereRaw('user_access_customer_job.type = parent_jobs.type');
    })
    ->pluck('parent_jobs.id');

改写说明 #

• 主查询直接以父作业表为查询主体，先过滤父作业维度的条件：排除指定作业ID、匹配指定客户ID
• 子查询承担原Join的关联匹配逻辑：校验存在符合要求的子作业记录，关联条件和原Join完全一致，包括父子作业ID关联、子作业排除指定ID、user_id null安全匹配、type字段匹配
• 原代码使用的lists是Laravel旧版本方法，5.2+版本框架替换为pluck，如果是老版本框架直接把末尾方法改回lists即可
• 保留了原SQL里的<=>null安全等于运算符，不会因为user_id字段为null出现匹配错误

内容的提问来源于stack exchange，提问作者rahul anand