Laravel Model Where Query A-Z

Laravel model is a part of MVC(Model View Controller) architecture that represents the data and business logic a for an application. Laravel model is mainly used for ORM(Object-Relational Mapper), Table Migration, Relationship building between different tables, timestamps management and so on. We can also use laravel model for managing accessors or mutators

In laravel model there are some built-in methods related to where. This where methods are used for managing model queries. Here in this blog we will use several where (where, whereIn, whereIn, whereNotIn) methods for interreacting and creating model queries. 

To get records that match a specific condition:

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

whereIn Clause

To get records where a column value is within a given array of values:

$users = User::whereIn('id', [1, 2, 3])->get();

whereNotIn Clause

To get records where a column value is not within a given array of values:

$users = User::whereNotIn('id', [1, 2, 3])->get();

orWhere Clause

To add an OR condition in query:

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

whereNull and whereNotNull Clauses

To check if a column is NULL or not NULL:

$usersWithNullEmail = User::whereNull('email')->get();
$usersWithNonNullEmail = User::whereNotNull('email')->get();

whereBetween and whereNotBetween Clauses

To get records where a column value is between a range:

$users = User::whereBetween('age', [25, 35])->get();
$users = User::whereNotBetween('age', [25, 35])->get();

whereDate, whereMonth, whereDay, whereYear

To filter records based on a date, month, day, or year:

$todayUsers = User::whereDate('created_at', now()->toDateString())->get();
$januaryUsers = User::whereMonth('created_at', '01')->get();
$firstDayUsers = User::whereDay('created_at', '01')->get();
$currentYearUsers = User::whereYear('created_at', now()->year)->get();

whereColumn Clause

To compare two columns:

$users = User::whereColumn('updated_at', '>', 'created_at')->get();

Using Closures for Complex where Conditions

For more complex queries, we can pass a closure to the where method:

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

Combining Multiple Where Clauses

You can chain multiple where clauses together:

$users = User::where('status', 'active')
             ->where('age', '>', 18)
             ->whereIn('role', ['admin', 'editor'])
             ->get();

Raw Expressions

For raw SQL expressions:

$users = User::whereRaw('age > ? AND status = ?', [18, 'active'])->get();

There are more use cases for using where clause in query builder. You can use that according to requirements