Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select all records from one table that do not exist in another table in Laravel 5.1

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

like image 792
Amrinder Singh Avatar asked Nov 26 '15 11:11

Amrinder Singh


People also ask

How do you select all records from one table that do not exist in another table in SQL Server?

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.


2 Answers

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();
like image 158
Sandeep J Patel Avatar answered Oct 29 '22 11:10

Sandeep J Patel


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!

like image 35
Murtaza Bharmal Avatar answered Oct 29 '22 09:10

Murtaza Bharmal