In this post, we have share tutorial on How to join multiple tables using Eloquent Model in Laravel framework and then after fetch data from multiple table and display on web page in HTML table format. So in this post you will find the solution of how to fetch data from multiple table by join multiple table using Eloquent Model under this Laravel framework. In this post we will use inner join for fetch data data from multiple table using Eloquent join table relationship. So by using this tutorial, you can learn How to retrieve data from multiple tables using join multiple table with eloquent model relationship in single query run under this Laravel 8 framework.
So If you want to learn How to Join Multiple Tables in Laravel framework using Eloquent Model relationship then this post will help you, because in this post you can find step by step guide for implement how can we implement inner join for multiple tables join using Eloquent model under this Laravel framework. Under this post we have use Eloquent model in place of simple Laravel join, this is because Laravel Eloquent model is more effective that simple Laravel join while we have to fetch data from multiple table in single query. So for this here we have use Eloquent model for join multiple table in Laravel framework. Below you can find step by step guide for how to join multiple table using Laravel eloquent model.
- Download Laravel Framework
- Make Database connection
- Create Model Class
- Create Controller Class
- Create View Blade file
- Set Route
- Run Laravel Server
Download Laravel Framework
For download fresh copy of Laravel framework, so first we have to into command prompt and run following command. This command will make join_table directory and under that directory it will download Laravel framework latest version.
composer create-project --prefer-dist laravel/laravel join_table
Make Database connection
After download and install Laravel framework and after this we want to make first database connection. So for make database connection, we have to open .env file and under this file, we have to define mysql database configuration. So it will create database connection in Laravel framework.
DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=testing
DB_USERNAME=root
DB_PASSWORD=
Once you have make database connection, then after we have to make table in mysql database. So for this you have to run following sql script in your local database, so it will create table in your define mysql database.
--
-- Table structure for table `city`
--
CREATE TABLE `city` (
`city_id` int(11) NOT NULL,
`state_id` int(11) NOT NULL,
`city_name` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `city`
--
INSERT INTO `city` (`city_id`, `state_id`, `city_name`) VALUES
(1, 1, 'New York city'),
(2, 1, 'Buffalo'),
(3, 1, 'Albany'),
(4, 2, 'Birmingham'),
(5, 2, 'Montgomery'),
(6, 2, 'Huntsville'),
(7, 3, 'Los Angeles'),
(8, 3, 'San Francisco'),
(9, 3, 'San Diego'),
(10, 4, 'Toronto'),
(11, 4, 'Ottawa'),
(12, 5, 'Vancouver'),
(13, 5, 'Victoria'),
(14, 6, 'Sydney'),
(15, 6, 'Newcastle'),
(16, 7, 'City of Brisbane'),
(17, 7, 'Gold Coast'),
(18, 8, 'Bangalore'),
(19, 8, 'Mangalore'),
(20, 9, 'Hydrabad'),
(21, 9, 'Warangal');
-- --------------------------------------------------------
--
-- Table structure for table `country`
--
CREATE TABLE `country` (
`country_id` int(11) NOT NULL,
`country_name` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `country`
--
INSERT INTO `country` (`country_id`, `country_name`) VALUES
(1, 'USA'),
(2, 'Canada'),
(3, 'Australia'),
(4, 'India');
-- --------------------------------------------------------
--
-- Table structure for table `state`
--
CREATE TABLE `state` (
`state_id` int(11) NOT NULL,
`country_id` int(11) NOT NULL,
`state_name` varchar(250) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `state`
--
INSERT INTO `state` (`state_id`, `country_id`, `state_name`) VALUES
(1, 1, 'New York'),
(2, 1, 'Alabama'),
(3, 1, 'California'),
(4, 2, 'Ontario'),
(5, 2, 'British Columbia'),
(6, 3, 'New South Wales'),
(7, 3, 'Queensland'),
(8, 4, 'Karnataka'),
(9, 4, 'Telangana');
--
-- Indexes for dumped tables
--
--
-- Indexes for table `city`
--
ALTER TABLE `city`
ADD PRIMARY KEY (`city_id`);
--
-- Indexes for table `country`
--
ALTER TABLE `country`
ADD PRIMARY KEY (`country_id`);
--
-- Indexes for table `state`
--
ALTER TABLE `state`
ADD PRIMARY KEY (`state_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `city`
--
ALTER TABLE `city`
MODIFY `city_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;
--
-- AUTO_INCREMENT for table `country`
--
ALTER TABLE `country`
MODIFY `country_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
--
-- AUTO_INCREMENT for table `state`
--
ALTER TABLE `state`
MODIFY `state_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;
Create Model Class
In Laravel framework, we have to create model class for database related operation. So for create model classe using compost, we have go to command prompt and run following command, this command will make model class file with name Country.php under app/Models folder.
php artisan make:model Country
After create model class, we have to open that file and under that file we have to define mysql table name details and column details.
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Country extends Model
{
use HasFactory;
protected $table = 'country';
protected $fillable = [
'country_name'
];
}
Create Controller Class
Under this Laravel framework, for handle HTTP request we have to create controller class. So for create controller using compost, we have go to command prompt and run following command.
php artisan make:controller JointableController
Once your controller class has been create then for open that file, we have go to app/Http/Controllers/JointableController.php file and under this file you have write following code for join multiple table using eloquent model and fetch data from multiple table in single query execution.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Models\Country;
class JointableController extends Controller
{
function index()
{
$data = Country::join('state', 'state.country_id', '=', 'country.country_id')
->join('city', 'city.state_id', '=', 'state.state_id')
->get(['country.country_name', 'state.state_name', 'city.city_name']);
/*Above code will produce following query
Select
`country`.`country_name`,
`state`.`state_name`,
`city`.`city_name`
from `country`
inner join `state`
on `state`.`country_id` = `country`.`country_id`
inner join `city`
on `city`.`state_id` = `state`.`state_id`
*/
return view('join_table', compact('data'));
}
}
?>
Create View Blade file
For display HTML output in browser, so we have to create view blade file in Laravel framework. In Laravel framework view blade file has been store under resources/views folder. Under this file, we have create join_table.blade.php file. Under this file, it will received data from controller class file. You can find source code of this view blade file in below.
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>How to Join Table in Laravel 8 using Eloquent Model</title>
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css" integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous">
</head>
<body>
<div class="container">
<br />
<h1 class="text-center text-primary">How to Join Multiple Table in Laravel 8 using Eloquent Model</h1>
<br />
<div class="table-responsive">
<table class="table table-bordered table-striped">
<thead>
<tr>
<th>Country</th>
<th>State</th>
<th>City</th>
</tr>
</thead>
<tbody>
@foreach($data as $row)
<tr>
<td>{{ $row->country_name }}</td>
<td>{{ $row->state_name }}</td>
<td>{{ $row->city_name }}</td>
</tr>
@endforeach
</tbody>
</table>
</div>
</div>
</body>
</html>
Set Route
Under this Laravel framework, we have to set route of the controller method. For set route in Laravel 8 framework, we have to open routes/web.php file. In Laravel 8 framework for set route, we have to first import our controller class under this web.php file. And for set route you can find source code below.
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\JointableController;
Route::get('/', function () {
return view('welcome');
});
Route::get('join_table', [JointableController::class, 'index']);
Run Laravel Server
After follow all above steps now all are set now we want to run Laravel application in browser. So for this, we have to go command prompt and run following command.
php artisan serve
Once you have run above command so after run this command it will provide us base url of our Laravel application. So for check above script output, we have to hit following url in browser.
http://127.0.0.1/join_table
