Updated April 11, 2023
Introduction to Laravel Join
The Laravel framework is one of the most sought after of frameworks in the tech world currently. The reason being its ease of usage and scalability. Its robust and yet simple methods of fetching functions have been winning hearts for some time now. Because of this reason, Laravel Framework have become the favourite for the creation of E Commerce sites. Clients have a wide variety of functionalities to choose from. The other reason Laravel framework is a sought after platform is because of its ability to facilitate third party integrations. It works seamlessly to create entire systems. In this article we will take a quick look into Laravel Join and see how helpful it is to create reports from multiple data points.
What is Laravel Join?
With the help of join queries in Laravel, one can add two or more tables. It is helpful in integrating large disparate sets of tables into one singular point of reference.
It is broken up into different segments:
Laravel Inner Join: This clause selects the records only if the selected values of the given column matches both the tables.
It is imperative that we are extremely careful with what we choose.
- Laravel Left Join Clause: The Laravel Left Join Clause will return all the values which are present in the left table, even though there are no matches in the right table. The right table will hence return a NULL.
- Laravel Right Join Clause: The Laravel Right Join clause will return all the values which are present in the right hand table, even though there are no matches in the table on the Left hand. The Left table will hence return a NULL.
- Laravel Cross Join Clause: The Laravel Cross Join clause allows every row from Table 1 to join with every row from Table 2.
- Laravel Advanced Join Clauses: In this clause, other functionalities can be included into the command set. Queries like “where” and “orwhere” can be used to compare column with values.
- Laravel Sub Query Joins: It is a continuation of the Advanced Join Clause, where a sub query is inserted to extend the functionality of the main query.
All these joins make the Join clause one of the most used queries.
Examples to Implement of Laravel Join
Let us look at an example that will make it amply clear.
Example #1
The Advanced join clause:
Code:
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
class getqueryController extends Controller
{
public function index(){
$students=DB::table('students')
->join('contacts', function ($join) {
$join->on('students.id', '=', 'contacts.student_id')
->where('contacts.student_id', '>', 3);
})
->get();
echo "<pre>";
print_r($students);
echo "</pre>";
}
}Output:
Example #2
Left Join Clause
Code:
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use Illuminate\Http\Request;
class getqueryController extends Controller
{
public function index(){
$students = DB::table('students')
->leftJoin('contacts', 'students.id', '=', 'contacts.student_id')
->select('students.id','students.name','contacts.phone','contacts.email')
->get();
echo "<pre>";
print_r($students);
echo "</pre>";
}
}Output:
Now this example was about integrating disparate set of tables and integrating it. Join table returns all the records or the rows which are present in both the tables.
Example #3
Let us look at the syntax :
Join('order', 'users.id','=','orders.user_id')- ‘order’: Identifier of the second table
- ‘users,id’: Key of the First Table
- ‘=’: operator
- ’orders.user_id’: Key of the Second Table
This is the breakup of the syntax of Inner Join clause:
Code:
// Use table "users" as first table
$usersOrders = DB::table('users')
// Perform a Join with the "orders"table, checking for the presence of matching
// "user_id"column in "orders"table and "id"column of the "user"table.
->join('orders', 'users.id', '=', 'orders.user_id')
// Retrieve users from the table as an array of objects containing users and
// products that each user has purchased
->get();
1 select * from `users` inner join `orders` on `users`.`id` = `orders`.`user_id`Output:
Let us look at the syntax of the Left Join clause:
leftJoin('order', 'users.id','=','orders.user_id')- ‘order’: Identifier of the second table
- ‘users,id’: Key of the First Table
- ‘=’: operator
- ’orders.user_id’: Key of the Second Table
This is the breakup of the Left Join Clause, Let us now have a look at an example signifying the same:
Code:
// Use table "users"as first table
$usersOrders = DB::table('users')
// Perform a Left Join with the "orders"table, checking for the presence of
// matching "user_id"column in "orders"table and "id"column of the "user" table.
->leftJoin('orders', 'users.id', '=', 'orders.user_id')
// Retrieve an array of objects containing records of "users" table that have
// a corresponding record in the "orders"table and also all records in "users"
// table that don’t have a match in the "orders"table
->get();
1 select * from `users` left join `orders` on `users`.`id` = `orders`.`user_id`Output:
Left joins are applicable especially in those cases when you want to have all the records from the first table even though none of records in the second table match. The Join clause can be used across a wide spectrum of functionality.
The same logic is applied in SQL. In fact, SQL being the precursor to PHP and the Laravel framework the idea is similar. The Join functionality will assimilate all the disparate tables and bring them together to get the relevant output. This can the:
Left Join:
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders LEFT JOIN Customers
ON Orders.CustomID=Customers.CustomerID;|
OrderID |
CustomerName |
OrderDate |
|
308 |
Ana Pushkova | 1990 |
| 334 | NULL |
1810 |
|
409 |
NULL |
2001 |
Right Join:
Code:
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders RIGHT JOIN Customers
ON Orders.CustomID=Customers.CustomerID;| OrderID | CustomerName |
OrderDate |
|
NULL |
John Chambers |
NULL |
|
308 |
Ana Pushkova | 1990 |
| NULL | Antonia Jussepe |
NULL |
Conclusion
From the above examples it is quite evident the efficacy of the Laravel Join clause. It is a helpful clause that enables the developer to fetch values from disparate sources of the table and create the required result. This is also another example of the flexibility of the Laravel framework.
Recommended Article
We hope that this EDUCBA information on “Laravel Join” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
