Laravel Eloquent's where clause in detail

• Understanding the Query Builder
• Crafting Simple Conditions
• Chaining Conditions
• Array-Based Conditions
• Utilizing OrWhere
• Conditional Wheres
• Eloquent Shortcuts
  • Dynamic Where Clauses
  • Combining Conditions
  • Using OR Conditions
  • Checking for NULL Values
  • Applying NOT Conditions
  • Checking for Non-NULL Values

Laravel's Query Builder is a gem in the world of web development. It simplifies the process of crafting SQL queries, making it more intuitive and less error-prone. One of its standout features is the 'Where' method, which streamlines complex queries. Let's delve into its intricacies.

¶Understanding the Query Builder

When you invoke a static method on a model, it's typically passed on to the Eloquent query builder. This is evident in the Builder class, where the 'where' method and its counterparts reside.

public static function __callStatic($method, $parameters) {
    return (new static)->$method(...$parameters);
}

public function __call($method, $parameters) {
    // ... other code ...

    return $this->forwardCallTo($this->newQuery(), $method, $parameters);
}

The forwardCallTo function directs the method call to $this->newQuery(), which produces an instance of the Builder class.

/**
 * Create a new query builder for the model's table.
 *
 * @return \Illuminate\Database\Eloquent\Builder
 */
public function newQuery() {
    return $this->registerGlobalScopes($this->newQueryWithoutScopes());
}

The 'where' method in the Builder class is versatile, handling a plethora of cases. Let's explore them.

¶Crafting Simple Conditions

The most straightforward way to frame a condition in Laravel is by supplying the key and value to 'where'.

User::where('email', '[email protected]')->first()

This translates to:

SELECT * FROM users WHERE `email` = '[email protected]' LIMIT 1

¶Chaining Conditions

You can concatenate multiple 'where' conditions for more specific queries.

User::where('email', '[email protected]')
	->where('first_name', 'Accred')->first()

This becomes:

SELECT * FROM users WHERE `email` = '[email protected]' AND `first_name` = 'Accred' LIMIT 1

¶Array-Based Conditions

You can also utilize an array to define multiple conditions.

User::where([
	'email' => '[email protected]',
	'last_name' => 'itly'
])->first()

This results in:

SELECT * FROM users WHERE `email` = '[email protected]' AND `last_name` = 'itly' LIMIT 1

¶Utilizing OrWhere

The 'OrWhere' method introduces an OR constraint to your queries.

User::where('email', '[email protected]')
	->orWhere('first_name', 'Accred')->first()

For more complex scenarios involving OR:

User::where(function($q) {
  $q->where('email', '[email protected]')
	  ->orWhere('last_name', 'itly');
})
->where('first_name', 'Accred')
->first()

This is equivalent to:

SELECT * FROM `users` WHERE (`email` = '[email protected]' or `last_name` = 'itly') AND `first_name` = 'Accred' LIMIT 1

¶Conditional Wheres

Apply specific conditions based on a boolean.

$isActive = true;

User::where('email', '[email protected]')
->when($isActive, function($q) {
	$q->where('first_name', 'Accred');
})->first()

¶Eloquent Shortcuts

Laravel provides shortcuts to make your life easier.

¶Dynamic Where Clauses

Using dynamic method names, you can quickly filter by column values:

User::whereEmail('[email protected]')->first()

This is equivalent to:

SELECT * FROM `users` WHERE `email` = '[email protected]' LIMIT 1

¶Combining Conditions

You can also combine multiple conditions using dynamic method names:

User::whereEmailAndFirstName('[email protected]', 'Accred')->first()

This translates to:

SELECT * FROM `users` WHERE `email` = '[email protected]' AND `first_name` = 'Accred' LIMIT 1

¶Using OR Conditions

For cases where you want to apply an OR condition:

User::whereEmailOrLastName('[email protected]', 'itly')->first()

This results in:

SELECT * FROM `users` WHERE `email` = '[email protected]' OR `last_name` = 'itly' LIMIT 1

¶Checking for NULL Values

To find records where a specific column is null:

User::whereNull('activated_at')->get()

This translates to:

SELECT * FROM `users` WHERE `activated_at` IS NULL

¶Applying NOT Conditions

For scenarios where you want to exclude certain values:

User::whereNot('status', 'inactive')->all()

This becomes:

SELECT * FROM `users` WHERE NOT `status` = 'inactive'

¶Checking for Non-NULL Values

A counterpart to the previous method:

User::whereNotNull('activated_at')->all()

This is equivalent to:

SELECT * FROM `users` WHERE `activated_at` IS NOT NULL

Eloquent is a very powerful ORM for Laravel which, when used properly can make your developer experience a more pleasant experience, but also a more productive one.