PHP Laravel Left Join Query Example

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, laravel 8, laravel 9 and laravel 10.

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...