时间戳

首页 > 解决方案 > 如何在 whereIn 中将两个查询合并为一个

laravel - 如何在 whereIn 中将两个查询合并为一个

问题描述

在我的 Laravel-5.8 中，我有以下查询：

$manager = DB::table('hr_employees')
    ->select('line_manager_id')
    ->where('hr_status',0)
    ->whereIn('employee_code', $employee_with_goals)
    ->get();

$employee_with_goals = DB::table('hr_employees')
    ->join('appraisal_goals', 'hr_employees.employee_code', '=', 'appraisal_goals.employee_code')
    ->select('hr_employees.employee_code')
    ->where('hr_employees.company_id', $userCompany)
    ->where('hr_employees.hr_status',0)
    ->where('appraisal_goals.is_published', '=', '1')
    ->where('appraisal_goals.is_approved', '=', '3')            
    ->groupBy('hr_employees.employee_code')
    ->get();

$manager 是主要查询

如何使用 $employee_with_goals 作为 whereIn() 中的变量将这两个查询合并为一个？

标签： laravel

解决方案

您可以使用该pluck方法检索集合中给定键的所有值：

$manager = DB::table('hr_employees')
    ->select('line_manager_id')
    ->where('hr_status',0)
    ->whereIn('employee_code', $employee_with_goals->pluck('employee_code'))
    ->get();

您实际上也可以使用子查询：

$manager = DB::table('hr_employees')
    ->select('line_manager_id')
    ->where('hr_status',0)
    ->whereIn('employee_code', function ($query) use ($userCompany) {
        $query
            ->table('hr_employees')
            ->select('hr_employees.employee_code')
            ->join('appraisal_goals', 'hr_employees.employee_code', '=', 'appraisal_goals.employee_code')
            ->where('hr_employees.company_id', $userCompany)
            ->where('hr_employees.hr_status',0)
            ->where('appraisal_goals.is_published', '=', '1')
            ->where('appraisal_goals.is_approved', '=', '3')            
            ->groupBy('hr_employees.employee_code')
    })
    ->get();

上面的代码未经测试。

推荐阅读