PHP Laravel Left Join Query Example

By Hardik Savani | July 31, 2020 | Category : Laravel


In this article we will cover on how to implement laravel left join query builder. We will use left join query in laravel eloquent. i would like to share with you how to use left join in laravel. let’s discuss about how to apply left join in laravel 6, laravel 7 and laravel 8.

If you are new in laravel and you don't know how to write left join in laravel application, then i will help you how to make left join in laravel application. you can easily use left join in laravel 6 and laravel 7 version.

Inner join is a main part of project. we almost require left join everywhere in project because of related table.

In this example, i will create users table and countries table. i will add country_id on users table and add countries table id on users table. so when i get users at that time we will get country name from country_id using left join.

So, let's see bellow example:

Example:

users Table:

countries Table:

SQL Query:

select `users`.`id`, `users`.`name`, `users`.`email`, `countries`.`name` as `country_name`

from `users`

left join `countries` on `countries`.`id` = `users`.`country_id`

Laravel Query:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\User;

class UserController extends Controller

{

/**

* Display a listing of the resource.

*

* @return \Illuminate\Http\Response

*/

public function index()

{

$users = User::select(

"users.id",

"users.name",

"users.email",

"countries.name as country_name"

)

->leftJoin("countries", "countries.id", "=", "users.country_id")

->get();

dd($users);

}

}

Output:

Array

(

[0] => Array

(

[id] => 1

[name] => Prof. Josiane Jast MD

[email] => savanihd@gmail.com

[country_name] => IN

)

[1] => Array

(

[id] => 2

[name] => Mr. Donavon Beer

[email] => yemmerich@example.net

[country_name] => US

)

[2] => Array

(

[id] => 3

[name] => Judy Watsica

[email] => gutmann.christian@example.org

[country_name] => CA

)

[3] => Array

(

[id] => 4

[name] => Miss Shakira Mosciski I

[email] => dillon.fay@example.org

[country_name] =>

)

)

Now i will give you best way to use inner join and get country name using Laravel Eloquent Relationship:

Example: Using Relationship

You have to create following models and controller method

i used belongsTo(), you can use hasOne, hasMany as you need:

app/User.php

<?php

namespace App;

use Illuminate\Contracts\Auth\MustVerifyEmail;

use Illuminate\Foundation\Auth\User as Authenticatable;

use Illuminate\Notifications\Notifiable;

class User extends Authenticatable

{

use Notifiable;

/**

* The attributes that are mass assignable.

*

* @var array

*/

protected $fillable = [

'name', 'email', 'password',

];

/**

* The attributes that should be hidden for arrays.

*

* @var array

*/

protected $hidden = [

'password', 'remember_token',

];

/**

* The attributes that should be cast to native types.

*

* @var array

*/

protected $casts = [

'email_verified_at' => 'datetime',

];

/**

* Get the comments for the blog post.

*/

public function country()

{

return $this->belongsTo(Country::class);

}

}

app/Country.php

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Country extends Model

{

}

Laravel Query:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\User;

class UserController extends Controller

{

/**

* Display a listing of the resource.

*

* @return \Illuminate\Http\Response

*/

public function index()

{

$users = User::select("*")

->with('country')

->get();

foreach ($users as $key => $value) {

echo $value->id;

echo $value->name;

echo $value->country->name ?? '-';

}

dd($users);

}

}

I hope it can help you...




Hardik Savani
My name is Hardik Savani. I'm a full-stack developer, entrepreneur and owner of Aatman Infotech. I live in India and I love to write tutorials and tips that can help to other artisan. I am a big fan of PHP, Javascript, JQuery, Laravel, Codeigniter, VueJS, AngularJS and Bootstrap from the early stage.
Follow Me: Github Twitter
***Do you want me hire for your Project Work? Then Contact US.

We are Recommending you: