Grouping eager-loading wherePivot constraints – impossible?

Consider the following table schema:

# Table members
id
name // etc.
joined // nullable datetime
resigned // nullable datetime

# Table roles
id
description // e.g. Chairman, President, Treasurer, etc.

# Table member_role
id
member_id
role_id
start // datetime (not nullable)
end // nullable datetime

Various member roles are defined, and any user can hold any of those roles for a period of time. What I’m trying to do is create a time-constrained view where the roles held by each user within the timespan in question are loaded – but not others. I’m currently doing this, which works:

// Let’s create a view for the period spanning January and February of 2022
$start = "2022-01-01";
$end   = "2022-02-28";

$members = Member::where('joined', '<=', $end)
	->where(function ($query) use ($start) {
		$query->where('resigned', '>=', $start)
			->orWhereNull('resigned');
	})
	->with(['roles' => function($query) use ($start, $end) {
		$query->wherePivot('start', '<=', $end)
			->wherePivot('end', '>=', $start)
	}])
	->get();

This loads all members who were a member within the January–February period and eager-loads any roles they had within the same period – except roles which are still active, since those will have end = NULL in the member_role table. Easy-peasy, says I; I’ll just do this:

$members = Member::where('joined', '<=', $end)
	->where(function ($query) use ($start) {
		$query->where('resigned', '>=', $start)
			->orWhereNull('resigned');
	})
	->with(['roles' => function($query) use ($start, $end) {
		->wherePivot('start', '<=', $end)
		->where(function ($query) use ($start) {
			$query->wherePivot('end', '>=', $start)
				->orWherePivotNull('end');
		});
	}])
	->get()

– but no. The $query object that gets passed to the closure in where() is a Builder object, which doesn’t have a wherePivot() method, as opposed to the $query object in the with() closure, which is a BelongsToMany object that does have a wherePivot() method.

Actually, my very first thought was to just nest the two wherePivot() conditions inside a closure passed to the original wherePivot() call, but it seems wherePivot() doesn’t accept closures like where() does – it requires explicit column names and values.

Okay then, says I; I’ll do this instead:

$members = Member::where('joined', '<=', $end)
	->where(function ($query) use ($start) {
		$query->where('resigned', '>=', $start)
			->orWhereNull('resigned');
	})
	->with(['roles' => function($query) use ($start, $end) {
		$query->withPivot('end')
		->wherePivot('start', '<=', $end)
		->where(function ($query) use ($start) {
			$query->where('pivot_end', '>=', $start)
				->orWhereNull('pivot_end');
		});
	}])
	->get()

– but also no. This code creates the following SQL query for fetching the roles relationship:

select 
	[roles].*, 
	[member_role].[member_id] as [pivot_member_id], 
	[member_role].[role_id] as [pivot_role_id], 
	[member_role].[start] as [pivot_start], 
	[member_role].[end] as [pivot_end] 
from 
	[roles] 
	inner join 
		[member_role] 
	on 
		[roles].[id] = [member_role].[role_id] 
where 
	[member_role].[member_id] in (1, 2, 3, 4, 5) 
and 
	[member_role].[start] <= '2022-01-01 '
and (
	[pivot_end] >= '2022-02-28'
	or [pivot_end] is null
)

This is not valid SQL, since you can’t use the column alias in the where clause like this.

In this particular case, I can work around it by just calling the offending column end in the code, since I know there aren’t any other columns called end in the query. But that’s obviously not the right way this should be done – if there had been an end column in the roles table as well, the query would fail.

But I’ll be buggered if I can think of another way to achieve this.

Is there no way at all to group pivot table constraints when eager loading relationships?