Category Archives: Laravel

Laravel Tutorials

Laravel where In Eloquent Query Example

In this laravel where in query with column example tutorial, I’ll show you how to use laravel eloquent whereIn() method to fetch data from laravel model using laravel query builder.

Laravel where In Eloquent Query Example

In this article, we will provides you simple example to demonstrate the use of laravel wherein method on query builder and mode.We will also use example to show how to use laravel eloquent wherein with arrays.

where In method syntax

whereIn(coulumn_name, Array);

1	whereIn(coulumn_name, Array);

Here,

column_name:- Your database table column name.

Array: – array with comma-separated values.

wherein Query Using Simple SQL Query:

SELECT *  FROM users  WHERE id IN (10, 15, 18)

1	SELECT * FROM users WHERE id IN (10, 15, 18)

The above query will be get data for ids 10, 15, 18 from users table.

Eloquent Wherein Query Using Query Builder

In this example we will be fetching users data using laravel whereIn clause with query builder.

public function index()
{
    $data = DB::table('users')
                ->whereIn('id', [10, 15, 18])
                ->get();
   
    dd($data);                    
}

public function index()

{

$data = DB::table('users')

->whereIn('id', [10, 15, 18])

->get();

dd($data);

}

Eloquent Wherein Query Using Laravel Model

In this example we will be fetching users data using laravel whereIn clause with laravel model.

public function index()
{
    $data= User::whereIn('id', [10, 15, 18])->get();
   
    dd($data);                    
}

public function index()

{

$data= User::whereIn('id', [10, 15, 18])->get();

dd($data);

}

Eloquent Wherein Query Using Laravel Model With Different Column Name

In this example we will be fetching users data using laravel whereIn clause with column names.

public function index()
{
    $data= User::whereIn('name', ['john','dam','smith'])->get();
   
    dd($data);                    
}

public function index()

{

$data= User::whereIn('name', ['john','dam','smith'])->get();

dd($data);

}

Laravel where Not In Eloquent Query Example

In this Laravel where Not In Eloquent Query Example tutorial, I’ll show you how to use laravel eloquent whereNotIn() method to fetch data from laravel model using laravel query builder.

Laravel whereNotIn Eloquent Query Example

In this article, we will provides you simple example to demonstrate the use of laravel whereNotIn method on query builder and mode.We will also use example to show how to use laravel eloquent whereNotIn with arrays.

whereNotIn method syntax

The Laravel whereNotIn method is mainly used to skip some records when fetching records from the database table.

whereNotIn(Coulumn_name, Array);

1	whereNotIn(Coulumn_name, Array);

Here,

column_name:- Your database table column name.

Array: – array with comma-separated values that you want to skip.

whereNotIn Query Using Simple SQL Query

SELECT *  FROM users  WHERE id NOT IN (10, 15, 18)

1	SELECT * FROM users WHERE id NOT IN (10, 15, 18)

The above sql query will fetch data from users table except the user with id 10, 15, 18.

Eloquent WhereNotIn Query Using Query Builder

In this example we will be fetching users data using laravel whereNotIn clause with query builder.

public function index()
{
    $data = DB::table('users')
                ->whereNotIn('id', [10, 15, 18])
                ->get();
   
    dd($data);                    
}

public function index()

{

$data = DB::table('users')

->whereNotIn('id', [10, 15, 18])

->get();

dd($data);

}

Eloquent WhereNotIn Query Using Laravel Model

In this example we will be fetching users data using laravel whereNotIn clause with laravel model.

public function index()
{
    $data= User::whereNotIn('id', [10, 15, 18])->get();
   
    dd($data);                    
}

public function index()

{

$data= User::whereNotIn('id', [10, 15, 18])->get();

dd($data);

}

Eloquent WhereNotIn Query Using Laravel Model With Different Column Name

In this example we will be fetching users data using laravel whereNotIn clause with laravel column names.

public function index()
{
    $data= User::whereNotIn('name', ['john','dam','smith'])->get();
   
    dd($data);                    
}

public function index()

{

$data= User::whereNotIn('name', ['john','dam','smith'])->get();

dd($data);

}

Laravel whereIn, whereNotIn With SubQuery Example

In this Laravel whereIn and whereNotIn with subquery example article, I’ll show you how to use whereIn and whereNotIn subquery in laravel queries.

Laravel SubQuery

In laravel, sometimes you may want fetch records from multiple database tables having complex and nested relationships. You may also require to exclude some nested data from DB table. So this can be achieved using whereIn and WhereNotIn clause in subquery.

Laravel WhereIn SubQuery

In the given example query we will fetch data from user table, which is available in the user_role table using whereIn subquery.

WhereIn SubQuery Using Query Builder

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

DB::table('users')->whereIn('id', function($query) {

$query->select('user_id')->from('role_user');

})->get();

WhereIn SubQuery Using Model

User::whereIn('id', function($query) {
    $query->select('user_id')->from('role_user');
})->get();

User::whereIn('id', function($query) {

$query->select('user_id')->from('role_user');

})->get();

The above laravel statement given whereIn subQueries represents following SQL query:

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

SELECT * FROM `users` WHERE `id` IN (

SELECT `user_id` FROM `role_user`

)

Laravel WhereNotIn SubQuery

In the given example query fetch data from user table, which is not available in the user_role table by using whereNotIn subquery.

whereNotIn SubQuery Using Query Builder

DB::table('users')->whereNotIn('id', function($query) {
    $query->select('user_id')->from('role_user');
})->get();

DB::table('users')->whereNotIn('id', function($query) {

$query->select('user_id')->from('role_user');

})->get();

WhereNotIn SubQuery Using Model

User::whereNotIn('id', function($query) {
    $query->select('user_id')->from('role_user');
})->get();

User::whereNotIn('id', function($query) {

$query->select('user_id')->from('role_user');

})->get();

The above laravel statement given whereNotIn subQueries represents following SQL query:

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

SELECT * FROM `users` WHERE `id` NOT IN (

SELECT `user_id` FROM `role_user`

)

Laravel WhereHas Eloquent Example

In this Laravel WhereHas Eloquent Example tutorial, we will learn about laravel eloquent whereHas() method with the example. In this tutorial we will also learn about laravel has() and with() eloquent methods.

Laravel Eloquent With Method

The Laravel with() method represent eager loading. The laravel with method preload the relationship(s) you specify along the main model. This is helpful when you have a collection of models and you want to load a relation for all of them. With eager loading you run only one additional DB query instead of one for every db model.

Example:-

User > hasMany > Post

$users = User::with('posts')->get();
foreach($users as $user){
    $users->posts; // posts is already loaded and no additional DB query is run
}

$users = User::with('posts')->get();

foreach($users as $user){

$users->posts; // posts is already loaded and no additional DB query is run

}

Laravel Eloquent has Meth

od

The eloquent has() method in laravel is used to filter the selecting model based on the selected relationship provided. It works like where method in laravel.

Example:-

User > hasMany > Post

$users = User::has('posts')->get();
// only users that have at least one post are contained in the collection

1 2	$users = User::has('posts')->get(); // only users that have at least one post are contained in the collection

Laravel Eloquent WhereHas Method

The eloquent whereHas() method in laravel works like the has() method but it just allows you to specify some additional filters for related laravel model to check in laravel.

Example:-

User > hasMany > Post

$users = User::whereHas('posts', function($q){
    $q->where('created_at', '>=', '2020-01-01 00:00:00');
})->get();
// only users that have posts from 2020 on forward are returned

$users = User::whereHas('posts', function($q){

$q->where('created_at', '>=', '2020-01-01 00:00:00');

})->get();

// only users that have posts from 2020 on forward are returned

where Between Laravel Eloquent Query

In this laravel whereBetween eloquent tutorial, I’ll show you how to fetch data between provided range from table in laravel.

whereBetween Laravel Eloquent Query

The laravel whereBetween clause is used to query data between provided range for specific column criteria. Suppose you want to know How many users have registered between two dates range. You can query this data using whereBetween eloquent methods.

whereBetween query With Ids

$users = User::whereBetween('id', [1, 50])->get();

1	$users = User::whereBetween('id', [1, 50])->get();

This laravel eloquent query gets the users between provided id 1 to 50 from users table.

Laravel Eloquent wherebetween Dates

Suppose you want to fetch all records from database between two dates, you can pass the start date and end date in whereBetween clause along with the column name.

$users = User::whereBetween('created_at', [start_date, end_date])->get();

1	$users = User::whereBetween('created_at', [start_date, end_date])->get();

The above laravel whereBetween query fetches the rows between start date to end date provided.

Laravel whereNotBetween Query Example

In this laravel where not between eloquent query example tutorial, I’ll show you how to fetch data from database table skipping data in the provided between range using laravel whereNotBetween eloquent query.

Laravel whereNotBetween eloquent query

The laravel whereNotBetween eloquent queryis used to fetch data from database table skipping data in the provided between range. Suppose, you want to fetch some data from database in laravel and you may want to skip some records between two column values. This can be achieved using laravel eloquent whereNotBetween cluase.

Laravel whereNotBetween query With Ids

$users = User::whereNotBetween('id', [1, 50])->get();

1	$users = User::whereNotBetween('id', [1, 50])->get();

The above laravel eloquent query fetches all records from the users table skipping records between id 1 to 50 from users table.

Laravel Eloquent whereNotBetween Dates

$users = User::->whereNotBetween('created_at', [start_date, end_date])->get();

1	$users = User::->whereNotBetween('created_at', [start_date, end_date])->get();

The above laravel eloquent query get all records from database table. But it skips records between given start date to end date.

Laravel Multiple Where Conditions Example

In this Laravel Multiple Where Conditions Example tutorial, I’ll show you how to use multiple where clause in laravel eloquent query.

Laravel Multiple Where Conditions Example

In laravel, when you fetch data from database tables you come to situation when you need to pass multiple conditions in laravel query. In laravel eloquent queries this can be done by providing multiple where clause in laravel eloquent query. In this tutorial, you will learn how to create and use multiple where clause query using laravel eloquent. Laravel eloquent model allows you to provide multiple where conditions to fetch required data. In this tutorial you will learn to use multiple where conditions with eloquent queries.

In this article we will share various examples of how to add multiple where clause with laravel search query with multiple conditions, update query with multiple conditions, delete query with multiple conditions, and relationship with multiple conditions in laravel.

Laravel multiple where clause

In this example we will be using Multiple where conditions in laravel query:

public function index()
{
    $users = User::where([["status" => 1], ["is_banned" => 0]])
                ->get(['name']);
    
    dd($users);
}

public function index()

{

$users = User::where([["status" => 1], ["is_banned" => 0]])

->get(['name']);

dd($users);

}

Laravel multiple where with Relationship

Open your model file and create scopes as following:

public function scopeActive($query)
 
{
 
    return $query->where('active', '=', 1);
 
}
 
 
 
public function scopeThat($query)
 
{
 
    return $query->where('that', '=', 1);
 
}

public function scopeActive($query)

{

return $query->where('active', '=', 1);

}

public function scopeThat($query)

{

return $query->where('that', '=', 1);

}

Then call scops as following:

public function index()
{
    //Then call the scopes as given below
 
    $users = User::active()->that()->get();
}

public function index()

{

//Then call the scopes as given below

$users = User::active()->that()->get();

}

Laravel orWhere Condition with Eloquent Query Example

In this Laravel orWhere conditions with eloquent query example tutorial, I’ll show you how to use laravel orWhere method in laravel query builder and model. In this article you will also learn to use laravel orWhere with multiple conditions. In this tutorial I’ll share different use case of laravel orWhere eloquent method. In this Laravel orWhere conditions with eloquent query tutorial you will learn about the syntax and use of Laravel orWhere method.

Laravel orWhere clause syntax

Here is the general syntax of the laravel orWhere method:

orWhere(Coulumn_name, Value);

1	orWhere(Coulumn_name, Value);

Laravel orWhere Query

In this example query we will learn to use orWhere clause with single and multiple where condition.

Laravel orWhere with Query Builder

In this example you will learn to use orWhere clause with laravel query builder. You can use laravel orWhere with query builder as following:

Example:-

public function index()
{
    $users = DB::table('users')
                    ->where('id', 1)
                    ->orWhere('email', 'example@gmail.com')
                    ->get();
   
    dd($users);                    
}

public function index()

{

$users = DB::table('users')

->where('id', 1)

->orWhere('email', 'example@gmail.com')

->get();

dd($users);

}

Laravel orWhere with Eloquent Model

In this example you will learn to use orWhere clause with laravel eloquent model. You can use laravel orWhere with eloquent model as following:

public function index()
{
    $users = User::where('id', 1)
                    ->orWhere('email', 'example@gmail.com')
                    ->get();
   
    dd($users);                    
}

public function index()

{

$users = User::where('id', 1)

->orWhere('email', 'example@gmail.com')

->get();

dd($users);

}

The above given whereNull query you will get the following SQL query:

SELECT * FROM users WHERE id = '1' OR email = 'example@gmail.com'

1	SELECT * FROM users WHERE id = '1' OR email = 'example@gmail.com'

Laravel Multiple orWhere

In this example you will learn to use multiple orWhere clause. You can use multiple orWhere with eloquent model as following:

public function index()
{
$users = User::where('name', 'like' , '%'.$qry.'%')
   ->orWhere(function($query) use($qry) {
        $query->where('email','like','%'.$qry.'%')
              ->where('status','!=',$qry) 
   })->get();
   dd($users);
}

public function index()

{

$users = User::where('name', 'like' , '%'.$qry.'%')

->orWhere(function($query) use($qry) {

$query->where('email','like','%'.$qry.'%')

->where('status','!=',$qry)

})->get();

dd($users);

}

Laravel Where Null and Where Not Null Query

In this Laravel where Null and where Not Null example tutorial I’ll show you how to use whereNull() and whereNotNull() methods in laravel. In this tutorial you will learn to use whereNull() and whereNotNull() methods for creating laravel eloquent queries. In this article I’ll share different use case of the whereNull() and whereNotNull() methods in laravel. In this Laravel where Null and where Not Null tutorial you will learn about the syntax and the use of whereNull and whereNotNull eloquent methods.

Laravel whereNull method syntax

Here is the general syntax of the laravel whereNull method:

whereNull('columnName');

1	whereNull('columnName');

Laravel whereNotNull method syntax

Here is the general syntax of the laravel whereNotNull method:

whereNotNull('columnName');

1	whereNotNull('columnName');

Laravel whereNull Query

In this example query we will fetch data from database table for the users where the name field empty. In this query we will demonstrate the use of whereNull Query. The laravel whereNull Query can be used as following:

public function index()
{
    $users = User::whereNull('name')->get();
   
    dd($users);                    
}

public function index()

{

$users = User::whereNull('name')->get();

dd($users);

}

The above given whereNull query you will get the following SQL query:

SELECT * FROM users WHERE name IS NULL;

1	SELECT * FROM users WHERE name IS NULL;

Laravel whereNotNull Query

In this example query we will fetch data from database table for the users where the email_verified_at field is not empty in DB table:. In this query we will demonstrate the use of whereNotNull Query. The laravel whereNotNull Query can be used as following:

public function index()
{
    $users = User::whereNotNull('email_verified_at')->get();
   
    dd($users);                    
}

public function index()

{

$users = User::whereNotNull('email_verified_at')->get();

dd($users);

}

The above given whereNull query you will get the following SQL query:

SELECT * FROM users WHERE email_verified_at IS NOT NULL;

1	SELECT * FROM users WHERE email_verified_at IS NOT NULL;

Laravel where Like Query Example

In this Laravel where like query example tutorial I’ll show you how to use laravel whereLike() method with query builder in laravel. You also learn how to use whereLike() eloquent method in search query. I’ll also show you how to use laravel where like for multiple like condition. In this tutorial I’ll demonstrate you different use case of the laravel whereLike method with examples.

Laravel where Like Query use with Model

Laravel whereLike() method is simple implementation of MySQL Like keyword. In laravel you can use % wildcard character with whereLike condition in similar as you do in mysql like.

Example:-

public function index()
{
    $users = User::where('name','LIKE',"%{$search}%")->get();
   
    dd($users);                    
}

public function index()

{

$users = User::where('name','LIKE',"%{$search}%")->get();

dd($users);

}

The above given Where like query you will get the following SQL query:

SELECT * FROM `users` WHERE `name` LIKE '%search%';

1	SELECT * FROM `users` WHERE `name` LIKE '%search%';

Using macros with Like

In laravel you can define a macro using the macro static method in class you want to define the macro for. We need to define a macro for the Eloquent class, so we can extend it as following:

public function index()
{
  User::whereLike('username', $username)
   ->whereLike('email', $email)
   ->get();
}

public function index()

{

User::whereLike('username', $username)

->whereLike('email', $email)

->get();

}

Laravel whereLike with multiple columns using macros

In laravel when you want to search with multiple columns then you can exten this macro to support multiple columns as following:

Builder::macro('whereLike', function($columns, $search) {
  $this->where(function($query) use ($columns, $search) {
    foreach(array_wrap($columns) as $column) {
      $query->orWhere($column, $search);
    }
  });
 
  return $this;
});

Builder::macro('whereLike', function($columns, $search) {

$this->where(function($query) use ($columns, $search) {

foreach(array_wrap($columns) as $column) {

$query->orWhere($column, $search);

}

});

return $this;

});

Laravel Eloquent whereRaw Query Example

In this laravel where raw query example tutorial I’ll show you how to write whereraw and where DB raw query in laravel. In this article you will also learn to use the laravel whereRaw eloquent method to building query in laravel. I’ll also show you how to use laravel where raw with multiple conditions in eloquent queries.

Laravel whereRaw Query using Model

In this example I’ll show you how to use laravel whereRaw query in laravel model. In laravel you can use whereRaw with model as following:

public function index()
{
    $users = User::whereRaw('YEAR(created_at) = ?', [date('Y')])->get();
 
    dd($users);
}

public function index()

{

$users = User::whereRaw('YEAR(created_at) = ?', [date('Y')])->get();

dd($users);

}

The above given whereRaw query you will get the following SQL query:

select * from `users` where YEAR(created_at) = ?

1	select * from `users` where YEAR(created_at) = ?

whereRaw Query using Query Builder

In this example I’ll show you how to use laravel whereRaw query using Query Builder. In laravel you can use whereRaw with Query Builder as following:

public function index()
{
    $users = DB::table('users')->whereRaw('YEAR(created_at) == ? AND MONTH(created_at) == ?', [date('Y'),date('m')])->get();
                 
    dd($users);
}

public function index()

{

$users = DB::table('users')->whereRaw('YEAR(created_at) == ? AND MONTH(created_at) == ?', [date('Y'),date('m')])->get();

dd($users);

}

The above given whereRaw query you will get the following SQL query:

select * from `users` where YEAR(created_at) == ? AND MONTH(created_at) == ?

1	select * from `users` where YEAR(created_at) == ? AND MONTH(created_at) == ?

How to Use DB Raw Query in Laravel

In this Laravel db raw query example tutorial, I’ll show you how to use db raw query in laravel. In this tutorial you will also learn how to use DB::raw() with join() method in laravel eloquent model. In laravel DB::raw() eloquent method you can also add subquery in laravel queries. In laravel you can use db raw queries to insert, retrieve, update, and delete data into database table. In this article we will demonstrate you the different use case of the db raw queries.

Laravel retrieve all records using DB raw query

In laravel to retrieve all records you can use db raw query as following:

Example:-

$users = User::get();

1	$users = User::get();

Delete using DB raw query

To delete data from the database table you can use DB raw query as following:

Example:-

$users = User::where('columnname', columnvalue)->delete();

1	$users = User::where('columnname', columnvalue)->delete();

DB raw With Select Clause Query in laravel

Sometimes in laravel you come to situations where you would need raw queries in your controller or model. In various cases in laravel you may need to use a raw expression in a query. In laravel you can write raw queries using DB as following:

Example:-

$users = User::select(DB::raw('count(*) as user_count, status'))
                     ->where('status', '<>', 1)
                     ->groupBy('status')
                     ->get();

$users = User::select(DB::raw('count(*) as user_count, status'))

->where('status', '<>', 1)

->groupBy('status')

->get();

Laravel DB Raw Query With Join

You can implement raw join queries using DB raw as following:

$data = DB::table("products")
 
  ->select("products.*","product_stock.quantity_group")
 
  ->join(DB::raw("(SELECT 
 
      product_stock.id_product,
 
      GROUP_CONCAT(product_stock.quantity) as quantity_group
 
      FROM product_stock
 
      GROUP BY product_stock.id_product
 
      ) as product_stock"),function($join){
 
        $join->on("product_stock.id_product","=","products.id");
 
  })
 
  ->groupBy("products.id")
 
  ->get();
 
 
 
print_r($data);

$data = DB::table("products")

->select("products.*","product_stock.quantity_group")

->join(DB::raw("(SELECT

product_stock.id_product,

GROUP_CONCAT(product_stock.quantity) as quantity_group

FROM product_stock

GROUP BY product_stock.id_product

) as product_stock"),function($join){

$join->on("product_stock.id_product","=","products.id");

})

->groupBy("products.id")

->get();

print_r($data);

Using DB Raw Get Max Value in Laravel Query

In laravel you can get max values using db raw as following:

\DB::table('orders')->where('id', \DB::raw("(select max(`id`) from orders)"))->get();

1	\DB::table('orders')->where('id', \DB::raw("(select max(`id`) from orders)"))->get();