Where Not in Laravel

Updated July 3, 2023

Introduction to Where Not in Laravel

Laravel framework has been in demand for some time now. Its expressive command line structure has mesmerized the coding world. The framework is also known for its robust scalability. Laravel is one of the most sought-after frameworks for eCommerce solutions because of the sheer number of options it provides. One can use its vast library to create functionalities that will help the users. The best feature of Laravel is probably its ability to provide the developer with myriad options for the same query. This makes the job of the developer that much simpler. Expressive queries lead to greater flexibility and a better understanding of the need of the user and the functionality.

Examples to Implement Where Not in Laravel

One of the best examples of this would be the query where not:

Example #1

This is a query in SQL and can be written as one too.

Code:

select * from posts;

Output:

This is a table with a few details.

In SQL:

Code:

select * from posts
where title IS NOT NULL;

Output:

The same can be said in Laravel:

Code:

DB::table('posts')
->whereNotNull('title')
->get();

The result is the same.

Code:

select * from posts
where title IS NULL;

Output:

In Laravel, the same would be done:

Code:

DB::table('posts')
->whereNull('title')
->get();

Explanation: The result would be the same. The interesting thing about Laravel Query is that it is rooted to a great extent in the essence of SQL. Hence, if one is aware of the SQL structure, it becomes easier for that person to understand the Eloquent method. That does not mean that the person has to be aware of SQL. The Laravel framework is easy to work with and is user-friendly.

Example #2

Let us look into another comparison:

Code:

SELECT * FROM `users`
WHERE `id` IN (
SELECT `user_id` FROM `invite_users`
)

The Laravel query for the same will be looking like this:

Code:

$users = DB::table("users")->select('*')
->whereIn('id',function($query){
$query->select('user_id')->from('invite_users');
})
->get();

Explanation: The above was an example of the Where In Query

Example #3

Now let us look at the Where Not In Query:

Code:

SELECT * FROM `users`
WHERE `id` NOT IN (
SELECT `user_id` FROM `invite_users`
)

The Laravel Query for the same will look like this:

Code:

$users = DB::table("users")->select('*')
->whereNOTIn('id',function($query){
$query->select('user_id')->from('invite_users');
})
->get();

Explanation: The output will be the same.

Example #4

A fair comparison will be between WhereExists() and where not().

WhereExists() in Laravel:

Code:

//model
public function tasks()
{
return DB::table('users')
->whereExists(function ($query)
{
$query->select(DB::raw(1))
->from('tasks')
->whereRaw('tasks.name = users.id');
})
->orWhereExists(function ($query)
{
$query->select(DB::raw(1))
->from('tasks')
->whereRaw('tasks.details like "%mo%"');
})
->get();
}

In SQL:

Code:

select * from `users`
where exists (select 1 from `tasks` where tasks.name = users.id)
OR exists (select 1 from `tasks` where tasks.details like "%mo%")

And now the WhereNot(), which is nothing but the opposite of WhereExists():

Code:

//model
public function tasks()
{
return DB::table('users')
->whereNotExists(function ($query)
{
$query->select(DB::raw(1))
->from('tasks')
->whereRaw('tasks.name = users.id');
})
->toSQL();
}

The SQL query for the same would be:

Code:

select * from `users`
where NOT EXISTS (select 1 from `tasks` where tasks.name = users.id)

Explanation: What happens here is that WhereNot() simply looks for the results which are opposite to WhereExists().

Example #5

Some other modifications to it:

For multiple WhereNotExists():

Code:

//model
public function tasks()
{
return DB::table('users')
->whereNotExists(function ($query)
{
$query->select(DB::raw(1))
->from('tasks')
->whereRaw('tasks.name = users.id');
})
->orWhereNotExists(function ($query)
{
$query->select(DB::raw(1))
->from('tasks')
->whereRaw('tasks.details like "%mo%"');
})
->get();
}

The SQL Query for the same would be:

Code:

select * from `users`
where not exists (select 1 from `tasks` where tasks.name = users.id)
OR not exists (select 1 from `tasks` where tasks.details like "%mo%")

Another modification for the same would be:

Code:

$query = DB::table('users')
->whereNotExists(function ($query)
{
$query->select(DB::raw(1))
->from('tasks')
->whereRaw('tasks.name = users.id');
});

The SQL Query for the same would be:

Code:

select * from `users`
where not exists (select 1 from `tasks` where tasks.name = users.id)

Explanation: The important aspect of this query is the Where() clause, which acts as the direction finder. Any modification or manipulation happens in and around the Where() clause. It is simpler in the SQL query however, many more parameters can be added to the Laravel command line to make it more customized.

Example #6

The Laravel Framework, with its Eloquent command line structure, is responsive to the numerous customizations demanded by the developer.

Code:

$sub = DB::table('tasks')
->from('tasks')
->whereRaw('tasks.name = 107');

The SQL Query for the same would be:

Code:

select * from `tasks` where tasks.name = 107

We can customize it further by adding the addwhereexistquery()

Code:

//model
public function tasks()
{
$query = DB::table('users')
->whereNotExists(function ($query)
{
$query->select(DB::raw(1))
->from('tasks')
->whereRaw('tasks.name = users.id');
});
$sub = DB::table('tasks')
->from('tasks')
->whereRaw('tasks.name = 107');
return $query->addWhereExistsQuery($sub)
->get();
}

The SQL command line would look like this:

Code:

"select * from `users` where
not exists (select 1 from `tasks` where tasks.name = users.id)
AND Exists (select * from `tasks` where tasks.name = 107)"

Explanation: The were not() is an extension of the where() as discussed earlier and hence can be modified into various forms.

The above examples are an illustration of the immense capabilities that the Laravel Framework provides to its users. One of the reasons that Laravel has been able to emerge as a favored framework is that it is open source. Also, being free, everyone can avail of it. All a developer would need is a text editor and PHP installed on his PC. As we have witnessed that the knowledge of basic SQL is good enough to begin working on PHP. However, as I have mentioned earlier in this article, one may also begin working on PHP with no knowledge of SQL. PHP and, in extension, Laravel are user-friendly languages that allow the developer to build functions from scratch. As we have witnessed a few examples, we can safely say, with Laravels vast library and logical command line structure, it is a treat to be able to code in it.

Conclusion

The Where not() query is a logical query that searches for items in a given database. As suggested, there are many modifications to it. these modifications can provide myriad outcomes depending on the parameters provided.

Recommended Articles

We hope that this EDUCBA information on “Where Not in Laravel” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. Laravel Key Generate
2. Laravel Raw Query
3. Laravel Orderby
4. Laravel Pluck