Laravel Eloquent with two “WHERE NOT IN” in subquery

Question

I have this query that I am having trouble to write query in laravel eloquent ORM.

Appreciate if someone can help.

Here is SQL Expression:

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

Instead of executing 3 different queries you can use like shown below,

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();

This code will give the exact same query which is asked in the question, to check the query, use following code

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();

Output will be,

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 2

Try Something like this:

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();