Sometimes Laravel's normal join is not enough to fetch the required data. In this case, Laravel provides another joining process like Laravel subquery join. Laravel subquery join is almost like a join query but here we merge two queries and then make a join. The first query returns the query, not the result.
In this Laravel query builder tutorial, we will see Laravel subquery join with laravel subquery sum with group by with laravel db raw. So you will learn lots of things from this tutorial. You can use this query for any pattern like laravel wherein subquery like that.
Laravel subquery join uses Illuminate\Database\Query\JoinClause as a parameter to make a join query. Let's see how we can write a laravel subquery join query:
App\Http\Controllers\TutorialController.php
<?php
namespace App\Http\Controllers\Api;
use Carbon\Carbon;
use App\Models\Holiday;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;
use Illuminate\Database\Query\JoinClause;
class HolidayController extends Controller
{
public function getLeaveBalance(Request $request)
{
$leaveHistory = DB::table('LeaveHistory')
->where('UserID', $request->UserID)
->select('LeaveIndecator', DB::raw('SUM(LeaveAppDays) as TakenLeave'))
->whereBetween('LeaveAppFrom', [date('Y-01-01'), date('Y-12-31')])
->groupBy('LeaveIndecator');
return DB::table('LeaveRule')
->joinSub($leaveHistory, 'leave_history', function (JoinClause $join) {
$join->on('LeaveRule.LeaveFor', '=', 'leave_history.LeaveIndecator');
})
->get()
->map(function ($leave) {
return [
'leave_for' => $leave->LeaveDetails,
'leave_indicator' => $leave->LeaveIndecator,
'leave_per_year' => (int) $leave->LeaveDaysPerYear,
'already_leave_taken' => (int) $leave->TakenLeave
];
});
}
}
Read also: Laravel Calculate Distance Between Two Coordinates
Conclusion
I have tried to demonstrate the concept of laravel subquery join in this tutorial, we have seen laravel join in php laravel application. Now we know how to use laravel db raw with subquery join in Laravel.
