Implementing lateral join in Laravel

lateral join.md

Implementing lateral join in Laravel

Making of joinLateral macro

use Illuminate\Database\Query\Builder;
use Illuminate\Database\Query\Expression

Builder::macro('joinLateral', function ($query, $as, $type = 'inner') {
    [$query, $bindings] = $this->createSub($query);

    $expression = 'lateral ('.$query.') as '.$this->grammar->wrapTable($as).' on true';

    $join = $this->newJoinClause($this, $type, new Expression($expression));

    $this->joins[] = $join;

    $this->addBinding($bindings, 'join');

    return $this;
});

Using the macro

use App\Models\Login;
use App\Models\User;

User::query()
    ->select(['users.name', 'latest_logins.logged_in_at'])
    ->joinLateral(
        Login::whereColumn('logins.user_id', 'users.id')
            ->latest('logins.logged_in_at')
            ->limit(3),
        'latest_logins'
    )
    ->get();

Check out video implementing it

Lateral Join Implementation In Laravel