In this article we will learn to use left join in laravel eloquent and query builder. Laravel itself provide inbuilt method to left join the table like in MySQL or SQL based database we can do join multiple tables using leftJoin function.
For example if you have 10 rows and you want the join from other table then it will return the all rows from left table and the matching records from the right table.
Laravel eloquent or query builder leftJoin method do the same as MySQL left join function. Laravel left join accepts multiple parameters to the function and first parameter as table name and rest are columns constraints.
Syntax for left join in Laravel
DB::table('users')
->leftJoin('contacts', 'users.id', '=', 'contacts.user_id')
->leftJoin('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();
//or
Model::select('users.*', 'contacts.phone', 'orders.price')
->leftJoin('contacts', 'users.id', '=', 'contacts.user_id')
->leftJoin('orders', 'users.id', '=', 'orders.user_id')
->get(); // or first()
//or
Model::select('users.*', 'contacts.phone', 'orders.price' )
->leftJoin('contacts', function ($leftJoin) {
$leftJoin->on('users.id', '=', 'contacts.user_id')->On('`contacts`.`phone`','');
})
//
\DB::table('users')
->leftJoin('contacts', function ($join) {
$leftJoin->on(function($query){
$query->on('users.id', '=', 'contacts.user_id')
->orOn("contacts.phone",'users.phone');
});In the above syntax we have used 3 examples to show all possibilities to use leftJoin function in laravel eloquent.
First is using the Query Builder DB class and leftJoin method of eloquent. Second is Model with leftJoin function on eloquent.
Third example is advance leftJoin to add multiple conditions to join in laravel and the final one to use grouping in laravel join.
Result of above query will be:
select `users`.*, `contacts`.`phone`, `orders`.`price` from `users` left join `contacts` on `users`.`id` = `contacts`.`user_id` inner join `orders` on `users`.`id` = `orders`.`user_id`
//or
select `users`.*, `contacts`.`phone`, `orders`.`price` from `users` left join `contacts` on `users`.`id` = `contacts`.`user_id` or `contacts`.`phone` = `32372328` left join `orders` on `users`.`id` = `orders`.`user_id`
//or
select `users`.*, `contacts`.`phone`, `orders`.`price` from `users` left join `contacts` on (`users`.`id` = `contacts`.`user_id` or `contacts`.`phone` = `users`.`phone`) left join `orders` on `users`.`id` = `orders`.`user_id`Let’s understand laravel leftJoin in eloquent query with example
Example 1 – Laravel left join function query using eloquent leftJoin method
So in this example i will use laravel query builder or eloquent leftJoin method to do the left join. Simple join to execute join between the two tables
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\User;
class ArticleController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
\DB::table('users')
->leftJoin('contacts', 'users.id', '=', 'contacts.user_id')
->leftJoin('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();
}
}Output will be :
select `users`.*, `contacts`.`phone`, `orders`.`price` from `users` left join `contacts` on `users`.`id` = `contacts`.`user_id` left join `orders` on `users`.`id` = `orders`.`user_id`Example 2 – Advance join with multiple condition in leftJoin query
In this example i will use laravel advance left join to create multiple conditions in join
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\User;
class ArticleController extends Controller
{
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
\DB::table('users')
->leftJoin('contacts', function ($leftJoin) {
$leftJoin->on(function($query){
$query->on('users.id', '=', 'contacts.user_id')
->orOn("contacts.phone",'users.phone');
});
})
->leftJoin('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get()
}
}In the above example we have used users, contacts and orders table to create the join and also we have used multiple condition in join.
To make the grouping we have used closure functions.
Output will be :
select `users`.*, `contacts`.`phone`, `orders`.`price` from `users` left join `contacts` on (`users`.`id` = `contacts`.`user_id` or `contacts`.`phone` = `users`.`phone`) left join `orders` on `users`.`id` = `orders`.`user_id`Also Read : How to use join in Laravel eloquent query with example
