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)

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Jun 25 '17 at 10:48
Find a good answer at https://stackoverflow.com/a/46275818/2803948 — Morteza Rajabi, Sep 18 '17 at 09:26

score 28 · Answer 1 · edited Feb 27 '19 at 12:43

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`)

Sagar Gautam · Answer 2 · 2017-06-25T04:43:54.890

16

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

edited Jun 25 '17 at 04:43

answered Jun 25 '17 at 04:13

Sagar Gautam

9,049
6
53
84

Not working. I am trying to figure it out why. The about works in phpmyadmin but my it said "Unknown column 'cust' in 'field list'" – Wahsei Jun 25 '17 at 08:02
you have different column name in the customer table. If your problem has been solved please close the question – Sagar Gautam Jun 25 '17 at 08:51
5

This gets the desired data but isn't exactly what the question asked. It performs 3 separate queries. See Akshay Kulkarni's answer below to see how to do it in a single query using advanced wheres – Chris May 05 '19 at 07:31

score 3 · Accepted Answer · answered Jun 25 '17 at 08:08

I corrected the code below pluck('cust') to pluck('cust_name') and pluck('cust_no') to pluck('cust_code') and it works

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

score 0 · Answer 4 · answered Jun 20 '18 at 19:25

You could use exists or left join for better performance instead of sub queries on same table like in existing solution, there is no need for these 2 extra sub queries

SELECT DISTINCT cust, cust_no 
FROM delivery_sap d
WHERE EXISTS (
    SELECT 1
    FROM delivery_sap
    WHERE cust_name = d.cust OR cust_code = d.cust
)

OR

SELECT DISTINCT d.cust, d.cust_no 
FROM delivery_sap d
LEFT JOIN delivery_sap d1 ON d.cust = d1.cust_name OR d.cust = d1.cust_code 
WHERE d1.cust IS NULL

DB::table('delivery_sap as d')
    ->leftJoin('delivery_sap as d1', function ($join) {
        $join->on('d.cust','=','d1.cust_name')
             ->orWhere('d.cust', '=', 'd1.cust_code');
   })
    ->whereNull('d1.cust')
    ->select('cust', 'cust_no')
    ->distinct()
    ->get();

Laravel Eloquent with two “WHERE NOT IN” in subquery

4 Answers4

Linked