In this article we will learn to use join in laravel eloquent and query builder. Laravel itself provide inbuilt method to join the table like in MySQL or SQL based database we can do join multiple tables using join function.
laravel eloquent Join method by default use inner join. For example if you have 10 rows and you want the join from other table then it will only return the rows which satisfy both the tables.
Laravel eloquent or query builder join method do the same as MySQL inner join function. Laravel join accepts multiple parameters to the function and first parameter as table name and rest are columns constraints.
Here is the syntax
DB::table('users')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();
//or
Model::select('users.*', 'contacts.phone', 'orders.price')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->get(); // or first()
//or
Model::select('users.*', 'contacts.phone', 'orders.price' )
->join('contacts', function ($join) {
$join->on('users.id', '=', 'contacts.user_id')->On('`contacts`.`phone`','');
})
//
\DB::table('users')
->join('contacts', function ($join) {
$join->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 join function in laravel eloquent.
First is using the Query Builder DB class and join method of eloquent. Second is Model with join function on eloquent.
Third example is advance join 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` inner 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` inner join `contacts` on `users`.`id` = `contacts`.`user_id` or `contacts`.`phone` = `32372328` inner join `orders` on `users`.`id` = `orders`.`user_id`
//or
select `users`.*, `contacts`.`phone`, `orders`.`price` from `users` inner join `contacts` on (`users`.`id` = `contacts`.`user_id` or `contacts`.`phone` = `users`.`phone`) inner join `orders` on `users`.`id` = `orders`.`user_id`Let’s understand laravel join in eloquent query with example
Example 1 – Laravel join function query using eloquent join method
So in this example i will use laravel query builder or eloquent join method to do the inner 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')
->join('contacts', 'users.id', '=', 'contacts.user_id')
->join('orders', 'users.id', '=', 'orders.user_id')
->select('users.*', 'contacts.phone', 'orders.price')
->get();
}
}Output will be :
select `users`.*, `contacts`.`phone`, `orders`.`price` from `users` inner join `contacts` on `users`.`id` = `contacts`.`user_id` inner join `orders` on `users`.`id` = `orders`.`user_id`Example 2 – Advance join with multiple condition in join query
In this example i will use laravel advance 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')
->join('contacts', function ($join) {
$join->on(function($query){
$query->on('users.id', '=', 'contacts.user_id')
->orOn("contacts.phone",'users.phone');
});
})
->join('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` inner join `contacts` on (`users`.`id` = `contacts`.`user_id` or `contacts`.`phone` = `users`.`phone`) inner join `orders` on `users`.`id` = `orders`.`user_id`Also Read : How to use left join in Laravel eloquent query with example