CODEWITHSUNDEEP
laraveleloquent
Theodory
Theodory

Reputation: 397

How to use `whereDoesntHave` in laravel

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.

Upvotes: 1

Views: 953

Answers (2)

YannPl
YannPl

Reputation: 1322

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

Upvotes: 1

Marwane Ezzaze
Marwane Ezzaze

Reputation: 1057

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();

Upvotes: 0

Related Questions