How to use `whereDoesntHave` in laravel-CodePudding

I am trying to fetch users who don't have productions by checking the start date and end date ranges.

 $production = Production::find($id);

$users = User::whereDoesntHave('productions', function ($query) {
    return $query
        ->where(function ($q) {
            return $q->whereBetween('start_at', [$production->start_at, $production->end_at])
                ->whereBetween('end_at', [$production->start_at, $production->end_at]);
        });
})->get();

//User Production relation
public function productions()
{
    return $this->belongsToMany(Production::class)
        ->withPivot(['created_at', 'updated_at']);
}

This returns users who are already assigned in other productions within current production start date and end date that and I only want to fetch users who has no production assignment within the current date range.

CodePudding user response：

If I understand correctly, you have a Production model instance in the $production variable. You want all users that doesn't have production that overlap with the time range of the $production instance ?

Your current code (pasted from your question):

$production = Production::find($id);

$users = User::whereDoesntHave('productions', function ($query) {
    return $query
        ->where(function ($q) {
            return $q->whereBetween('start_at', [$production->start_at, $production->end_at])
                ->whereBetween('end_at', [$production->start_at, $production->end_at]);
        });
})->get();

What you should do :

$production = Production::find($id);

$users = User::whereDoesntHave('productions', function ($query) use($production) {
        return $query->whereBetween('start_at', [$production->start_at, $production->end_at])
                ->orWhereBetween('end_at', [$production->start_at, $production->end_at]);
    });
})->get();

I think the key thing here is include both start and end range in the result vith a orWhereBetween condition instead of whereBetween. And don't forget to use the $production variable in the closure ;-)

If this is not what you are trying to do, please comment to explain more precisely what you want to do

CodePudding user response：

You're passing the dates as parameters but you never actually use them, this is how it should be:

$users = User::whereDoesntHave('productions', function ($query) use ($startDate, $endDate) {
        return $query->where(function ($q) use ($startDate, $endDate) {
                return $q->whereBetween('start_at', [$startDate,$endDate])
                    ->orWhereBetween('end_at', [$startDate, $endDate]);
            });
    })
    ->get();