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.
Examples to use join in laravel
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`In Laravel, you can also perform an inner join operation on two or more database tables using the Eloquent ORM (Object-Relational Mapping) or the Query Builder. An inner join combines rows from two or more tables based on a related column between them, and it only includes the rows that have matching values in both tables.
Here’s how to perform an inner join in Laravel:
- Using Eloquent ORM:
Assuming you have two Eloquent models representing your tables, let’s call them Model1 and Model2, and you want to join them based on a common column, such as column_id:
phpCopy code
$result = Model1::join('model2', 'model1.column_id', '=', 'model2.column_id')
->select('model1.*', 'model2.some_column')
->get();Also Read : How to use left join in Laravel eloquent query with example
