How to Add a Where Condition to a Join in Laravel

Laravel provides advanced join clauses which allow us to add where condition directly inside a join clause.

To add advanced join clauses, we can pass a closure as the second argument to the join method. The closure receives a JoinClause object, which allows us to define the join condition and add a where condition.

For example, the following query retrieves all articles along with their approved comments only:

<?php

use Illuminate\Support\Facades\DB;
use Illuminate\Database\Query\JoinClause;

$articlesWithApprovedComments = DB::table('articles')
    ->join('comments', function (JoinClause $join) {
        $join->on('articles.id', '=', 'comments.article_id')
            ->where('comments.is_approved', true);
    })
    ->get();

The query above will produce the following SQL query:

select * from articles
inner join comments on articles.id = comments.article_id and comments.is_approved = 1;

As shown, the where clause is embedded directly within the on condition of the join.