Laravel's Eloquent ORM provides a powerful toolset for interacting with databases in a seamless manner. This guide will dive into the effective usage of where clauses in Eloquent to construct optimal queries for your Laravel applications. By understanding how to utilize where, and-or conditions, and whereNot clauses, you'll be equipped to enhance your querying skills at any level of expertise.

Basic where Clause

The fundamental building block is the where clause, used in Eloquent queries to retrieve data based on specific conditions. It can be appended to your query, accepting one or more conditions.

Post::where('is_published', '==', 'true')->get(); 

//or for covinience can be written like the below
Post::where('is_published','true')->get();

You may chain multiple where clauses to a single query using arrow function like so:

Post::where('is_published', 'true')->where('id', '!<', '10')->get(); 
//or for convinience

Post::where([
	['is_published', 'true'],
	['id', '!<', '10']
])->get();

orWhere clause

The orWhere method allows you to add a condition to your query that will be considered alongside any previous where conditions using the logical OR operator. This means that the query will retrieve records that match either the previous conditions or the new condition specified with orWhere.

Let's walk through some code examples to fully understand the usage of orWhere clauses in real world.

Suppose we have a users table with columns name, email, and status. We want to retrieve users who have either an active or suspended status.

Example 1: Basic orWhere Usage

use App\Models\User;

$users = User::where('status', 'active')
             ->orWhere('status', 'suspended')
             ->get();

In this example, the query will retrieve all users who have either an "active" status or a "suspended" status.

Example 2: Combining Multiple Conditions using orWhere

$users = User::where('status', 'active')
             ->orWhere(function ($query) {
                 $query->where('status', 'pending')
                       ->orWhere('status', 'approved');
             })
             ->get();

In this example, the query will retrieve users with either an "active" status or those who have a "pending" or "approved" status.

Example 3: Mixing where and orWhere Conditions

 $users = User::where('status', 'active')
             ->where('email', 'example@example.com')
             ->orWhere('status', 'suspended')
             ->get();

 

In this example, the query will retrieve users who have either an "active" status and a specific email or those with a "suspended" status.

Example 4: Combining Multiple orWhere Clauses

$users = User::where('status', 'active')
             ->orWhere('status', 'suspended')
             ->orWhere(function ($query) {
                 $query->where('email', 'example@example.com')
                       ->orWhere('email', 'another@example.com');
             })
             ->get();
 

In this example, the query will retrieve users who have either an "active" status, a "suspended" status, or an email that matches "example@example.com" or "another@example.com".

Using orWhere clauses in Laravel gives you the flexibility to build complex queries that account for various conditions and their combinations. This makes it easier to retrieve the specific data you need from your database based on logical OR relationships between conditions.

whereNot

Certainly! In Laravel, the whereNot method allows you to add a condition to your query that excludes records based on the specified condition. It's used to retrieve records that do not match the given criteria.

Lets look at an example to further understand this concept:

Example 1: Basic whereNot Usage

use App\Models\User;

$users = User::whereNot('status', 'active')
             ->get();
 

In this example, the query will retrieve users whose status is not "active".

Example 2: Combining where and whereNot Conditions

 $users = User::where('status', 'active')
             ->whereNot('email', 'example@example.com')
             ->get();

 

In this example, the query will retrieve users who have an "active" status but do not have the email "example@example.com".

Example 3: Using whereNot with Column Values

$latestUsers = User::whereNot('created_at', '>', now()->subDays(30))
                   ->get();
 

In this example, the query will retrieve users whose creation date is not greater than 30 days ago, effectively fetching users created more than 30 days ago.

Example 4: Combining whereNot with Other Conditions

 $users = User::where('status', 'active')
             ->whereNot(function ($query) {
                 $query->where('email', 'example@example.com')
                       ->orWhere('email', 'another@example.com');
             })
             ->get();

 

In this example, the query will retrieve users with an "active" status but exclude those with email addresses "example@example.com" or "another@example.com".

Using whereNot in Laravel allows you to filter records that do not meet specific criteria, making your queries more precise and efficient.

WhereBetween orWhereBetween

Certainly! In Laravel, the whereBetween and orWhereBetween methods are used to add conditions to your query that filter records within a specified range of values. They are particularly useful when you want to retrieve records that fall within a certain numeric or date range.

Here are explanations and code examples for both whereBetween and orWhereBetween:

Example 1: whereBetween Usage

 use App\Models\Transaction;

$transactions = Transaction::whereBetween('amount', [100, 1000])
                           ->get();

 

In this example, the query will retrieve transactions where the amount column falls between 100 and 1000 (inclusive).

Example 2: orWhereBetween Usage

 $transactions = Transaction::where('status', 'completed')
                           ->orWhereBetween('amount', [500, 2000])
                           ->get();

 

In this example, the query will retrieve transactions with a "completed" status or where the amount column falls between 500 and 2000 (inclusive).

Example 3: Combining whereBetween with Other Conditions

 $transactions = Transaction::where('status', 'completed')
                           ->whereBetween('created_at', ['2023-01-01', '2023-07-31'])
                           ->get();

 

In this example, the query will retrieve transactions with a "completed" status that were created between January 1, 2023, and July 31, 2023.

Example 4: Using orWhereBetween with Column Values

 $users = User::where('status', 'active')
             ->orWhereBetween('created_at', [now()->subDays(30), now()])
             ->get();

 

In this example, the query will retrieve users with an "active" status or those whose created_at falls within the last 30 days.

Using whereBetween and orWhereBetween in Laravel allows you to easily filter records within specific ranges, whether they involve numeric values, dates, or any other sortable data type. These methods help you create more precise queries when dealing with range-based criteria.

whereExist

In Laravel, the whereExists method is used to add a condition to your query that checks for the existence of records in a related table based on a subquery. This is particularly useful when you want to filter records based on the presence of related data in another table.

Here's an explanation of the whereExists clause with a code example:

Example: Using whereExists Suppose you have a posts table and a comments table, and you want to retrieve posts that have at least one associated comment.

 use App\Models\Post;

$postsWithComments = Post::whereExists(function ($query) {
    $query->select(DB::raw(1))
          ->from('comments')
          ->whereColumn('comments.post_id', 'posts.id');
})
->get();

 

In this example, the query will retrieve posts that have at least one related comment. The whereExists clause contains a subquery that checks if there's a row in the comments table with a matching post_id.

You can further enhance this example by adding additional conditions to the subquery or by combining whereExists with other clauses for more complex filtering.

The whereExists clause is useful when you need to filter records based on the existence of related data, allowing you to create advanced queries that consider data relationships within your database.

Conclusion

In this tutorial, we learned how to use multiple WHERE AND-OR conditions in Laravel queries. You can now efficiently filter and retrieve data from your database based on various criteria. Understanding these techniques will help you optimize your Laravel applications and become a more proficient mid-level Laravel developer. Experiment with different scenarios to deepen your understanding and proficiency in Laravel querying. Happy coding!