Laravel雄辩与两个"WHERE not IN"在子查询

Question

我有一个问题，我很难用laravel雄辩的ORM编写这个问题。

如果有人能帮忙，我将不胜感激。

下面是SQL表达式：

SELECT DISTINCT cust, cust_no FROM delivery_sap 
WHERE cust NOT IN ( SELECT cust_name FROM customer) 
AND cust_no NOT IN ( SELECT cust_code FROM customer)

Answer 1

我把下面的代码从pull（'cust'）改为pull（'cust_name'），从pull（'cust_no'）改为pull（'cust_code'），效果很好

DB::table('delivery_sap')
    ->whereNotIn('cust', DB::table('customer')->pluck('cust_name'))
    ->whereNotIn('cust_no', DB::table('customer')->pluck('cust_code'))
    ->select('cust', 'cust_no')
    ->groupBy('cust', 'cust_no')
    ->get();

Answer 2

试着这样做：

DB::table('delivery_sap')
    ->whereNotIn('cust', DB::table('customer')->pluck('cust'))
    ->whereNotIn('cust_no', DB::table('customer')->pluck('cust_no'))
    ->select('cust', 'cust_no')
    ->groupBy('cust', 'cust_no')
    ->get();

Answer 3

而不是执行3个不同的查询，您可以使用如下所示，

DB::table('delivery_sap')
->whereNotIn('cust', function ($query) {
        $query->select('cust_name')->from('customer');
    })
->whereNotIn('cust_no', function ($query) {
        $query->select('cust_code')->from('customer');
    })
->select('cust', 'cust_no')
->distinct('cust')
->get();

此代码将给出与问题中所问完全相同的查询，要检查查询，请使用以下代码

DB::table('delivery_sap')
->whereNotIn('cust', function ($query) {
        $query->select('cust_name')->from('customer');
    })
->whereNotIn('cust_no', function ($query) {
        $query->select('cust_code')->from('customer');
    })
->select('cust', 'cust_no')
->distinct('cust')
->toSql();

产量将是,，

select distinct `cust`, `cust_no` from `delivery_sap` 
where `cust` not in (select `cust_name` from `customer`) 
and `cust_no` not in (select `cust_code` from `customer`)