Laravel Self Join

Laravel provides a powerful feature called “Self Join” which allows you to join a table with itself. Self join is used when you want to combine rows from a table with other rows from the same table.

To explain it further, let’s consider an example:

Employees Table Structure:

In this example, the “employees” table has a “supervisor_id” column which references to the “id” column of the same table. This means that each employee can have a supervisor (another employee).

Now, let’s say we want to retrieve the employee name along with the name of their supervisor. We can achieve this using self join in Laravel.

Here’s an example query that demonstrates how to use self join in Laravel:

use Illuminate\Support\Facades\DB;

$employees = DB::table('employees AS e1')
    ->select('e1.name AS employee_name', 'e2.name AS supervisor_name')
    ->join('employees AS e2', 'e1.supervisor_id', '=', 'e2.id')
    ->get();

In this example, we are joining the “employees” table with itself using aliases “e1” and “e2”. We select the employee name as “employee_name” and the name of their supervisor as “supervisor_name”. The join condition is specified as “e1.supervisor_id = e2.id”, which means we are joining the “supervisor_id” of “e1” table with the “id” of “e2” table.

The result of this query will give us the following output:

This output shows us the names of employees along with their respective supervisors.

So, this is how you can use self join in Laravel to combine rows from a table with other rows from the same table. It provides a powerful way to work with hierarchical data structures.