How To Join Sub Queries With Example Using Laravel

admin_img Posted By Bajarangi soft , Posted On 23-09-2020

The query builder may also be used to write join statements. To perform a basic "join", you may use the join method on a query builder instance. The first argument passed to the join method is the name of the table you need to join to, while the remaining arguments specify the column constraints for the join. You can even join to multiple tables in a single query

How to Joins Subquery In Laravel

Subquery Joins

You may use the joinSubleftJoinSub, 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');

Related Post