Posted By
Bajarangi soft ,
Posted On 23-09-2020
You may use the joinSub, leftJoinSub, and rightJoinSub methods to join a query to a subquery. Each of these methods receive three arguments: the subquery, its table alias, and a Closure that defines the related columns:
Example(1)
app/Http/Controllers/SubqueryJoinTableController.php
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use DB;
class SubqueryJoinTableController extends Controller
{
function index()
{
$latestPosts = DB::table('posts')
->select('user_id', DB::raw('MAX(created_at) as last_post_created_at'))
->where('is_published', true)
->groupBy('user_id');
$users = DB::table('users')
->joinSub($latestPosts, 'latest_posts', function ($join) {
$join->on('users.id', '=', 'latest_posts.user_id');
})->get();
return view('user.index', ['users' => $users]);
}
}
?>
resources/views/user/index.blade.php
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Join Subquery In Laravel</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<br />
<h3 align="center">Join Subquery In Laravel</h3>
<br />
<div class="table-responsive">
<table class="table table-bordered table-striped">
<thead>
<tr>
<th>user_id</th>
<th>last_post_created_at</th>
<th>CourseName</th>
</tr>
</thead>
<tbody>
@foreach($users as $row)
<tr>
<td>{{ $row->user_id }}</td>
<td>{{ $row->last_post_created_at }}</td>
</tr>
@endforeach
</tbody>
</table>
</div>
</div>
</body>
</html>
routes/web.php
Route::get('SubqueryJoin', 'SubqueryJoinTableController@index');