Left Join Query in Laravel Eloquent Example

This simple article demonstrates of how to use left join in laravel. In this article, we will implement a how to apply left join in laravel. if you want to see example of how to make left join in laravel then you are a right place. Here you will learn how to write left join in laravel. Alright, let’s dive into the steps.

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