Laravel Eloquent with two “WHERE NOT IN” in subquery
21,374
Solution 1
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();
Solution 2
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`)
Solution 3
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();
Related videos on Youtube
Author by
Wahsei
Updated on July 09, 2022Comments
-
Wahsei almost 2 years
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)
-
Sagar Gautam almost 7 yearsI have updated try new answer
-
Strawberry almost 7 years
-
Morteza Rajabi over 6 yearsFind a good answer at stackoverflow.com/a/46275818/2803948
-
-
Wahsei almost 7 yearsNot working. I am trying to figure it out why. The about works in phpmyadmin but my it said "Unknown column 'cust' in 'field list'"
-
Sagar Gautam almost 7 yearsyou have different column name in the customer table. If your problem has been solved please close the question
-
Chris almost 5 yearsThis 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