Exploring the Power of Laravel Eloquent Join?

We’ll explore different join methods of Laravel eloquent with examples. The join helps to fetch the data from multiple database tables by establishing relationships between them.

What is ORM

Laravel’s Eloquent ORM (Object-Relational Mapping) is an ActiveRecord implementation that simplifies database communication with PHP applications. It allows developers to interact with database tables using PHP objects and methods, which eliminates the need to write complex SQL queries manually in the application.

We’ll discuss the following Laravel join methods:

• Inner Join: Returns only the matching rows from both tables.
• Left Join: Returns all rows from the left table and the matching rows from the right table.
• Right Join: Returns all rows from the right table and the matching rows from the left table.
• Full Outer Join: Returns all rows when there is a match in either the left or right table, It returns NULL values where there’s no match.
• Cross Join: Returns all rows from both tables.

Laravel Eloquent Join Method With Examples

Let’s assume you have an “employees” and a “departments” table, and you want to perform a join operation using Laravel’s Eloquent ORM. Define the relationship between Employee and Department models:

namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Employee extends Model
{
    public function department()
    {
        return $this->belongsTo(Department::class);
    }
}

class Department extends Model
{
    public function employees()
    {
        return $this->hasMany(Employee::class);
    }
}

Here’s a foreign key department_id in the employees table referencing the id column in the departments table.

There are the following types of join available in Laravel ORM. Let’s discuss them one by one with examples.

Inner Join

An inner join in Eloquent fetches records that have matching values in both tables. It filters out non-matching records from both table’s data.

$employees = Employee::join('departments', 'employees.department_id', '=', 'departments.id')
->select('employees.*', 'departments.name as department_name')
->get();

Left Join

A left join retrieves all records from the left table and matching records from the right table. It includes all records from the left table, regardless of whether corresponding matches are in the right table.

$employees = Employee::leftJoin('departments', 'employees.department_id', '=', 'departments.id')
->select('employees.*', 'departments.name as department_name')
->get();

Right Join

A right join fetches all records from the right table and matching records from the left table. It includes all records from the right table, regardless of whether there are corresponding matches in the left table.

$employees = Employee::rightJoin('departments', 'employees.department_id', '=', 'departments.id')
->select('employees.*', 'departments.name as department_name')
->get();

Cross Join

A cross join returns the Cartesian product of two tables, meaning it combines each row from the first table with every row from the second table.

$employees = Employee::crossJoin('departments')
->select('employees.*', 'departments.name as department_name')
->get();

Full Outer Join

Laravel does not support full outer join but you can achieve this by combining a left join and a union.

use App\Models\Employee;
use Illuminate\Support\Facades\DB;

$fullOuterJoinResults = Employee::leftJoin('departments', 'employees.department_id', '=', 'departments.id')
    ->select('employees.*', 'departments.name as department_name')
    ->unionAll(Employee::rightJoin('departments', 'employees.department_id', '=', 'departments.id')
    ->select('employees.*', 'departments.name as department_name'))
    ->get();

Conclusion:

We have covered Laravel eloquent join methods with examples. We have discussed left join, right join, cross join, outer join and inner join methods. You can use any of them as per your requirements.