I want to get all the records from a table which do not exist in other table in Laravel 5.1.
I know how to do this in core php, and it works fine with the following code
SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL
the model
public function audiences()
{
return $this->belongsTo('App\BridalRequest', 'request_id');
}
but when i try to do the same in Laravel by using the following code,
$all_bridal_requests_check = \DB::table('bridal_requests')
->where(function($query)
{
$query->where('publisher', '=', 'bq-quotes.sb.com')
->orWhere('publisher', '=', 'bq-wd.com-bsf');
})
->whereNotIn('id', function($query) { $query->table('audiences')->select('request_id'); })
->orderBy('created_on', 'desc')
->get();
then it gives me this error
Call to undefined method Illuminate\Database\Query\Builder::table()
How to Select All Records from One Table That Do Not Exist in Another Table in SQL? We can get the records in one table that doesn't exist in another table by using NOT IN or NOT EXISTS with the subqueries including the other table in the subqueries.
The above mentioned query can be built using laravel query builder in the following manner.
SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL
This is equivalent to below query built using Laravel's query builder.
\DB::table('table1 AS t1')
->select('t1.name')
->leftJoin('table2 AS t2','t2.name','=','t1.name')
->whereNull('t2.name')->get();
Late but yes I would like to add into this question. Create laravel eloquent relationship between table by writing below code in users
model file,
public function mu_user()
{
return $this->hasMany(\App\MainUser::class, 'id');
}
Now get all records from users table which does not have main user in relation as below,
$users = Users::doesnthave('mu_user')->get();
If you want to get users who have main_users then you can write query as below,
$users = Author::has('mu_user')->get();
It's such easy. Hope it helps!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With