CODEWITHSUNDEEP
phplaravellaravel-5laravel-5.5
Nidecker
Nidecker

Reputation: 190

php - Laravel - filter relation data inside relation

I'm trying to retrieve Player with user and calendars - Player hasOne user and Player hasMany calendars. But I need only male users - works fine, then I have to check calendars collision - select only players without calendar collision, query:

$freeplayers = Player::whereNotIn('id', $playersev)
                         ->with('user','calendars')
                         ->whereHas('user', function ($query) {
                            $query->where('gender', 'male');
                         })
                         ->whereHas('calendars', function ($query) use ($event) {
                             $query->whereNotIn('fulldate', $event->fulldate);
                         })
                         ->get();

Second whereHas is not working because:

So, I need male players with empty calendar (they are available) and players without collision in their existing calendar.

How can I achieve that? I tried to combine ->doesntHave('calendars')->orWhereHas('cale... but it's not working because if user have calendar, it is returned as another collection...

Thanks

Upvotes: 1

Views: 174

Answers (2)

NiRR
NiRR

Reputation: 5002

for complete API of the builder, use https://laravel.com/api/5.2/Illuminate/Database/Eloquent/Builder.html

you need to do a "sub query" in the sense that you want ONE of the following to occur: either the calendar is empty or the calendar date is not a specific date.

so 

Player::where(function($query) use ($forbiddenDatesArray) {
    $query->has('calendars', 0)
          ->orWhereHas('calendars', function($query) use ($forbiddenDatesArray){
              $query->whereNotIn('fullDate', $forbiddenDatesArray);
          });
});

Upvotes: 1

Abid Raza
Abid Raza

Reputation: 755

whereNotIn checks the value to the given array as second argument

Try

->whereHas('calendars', function ($query) use ($event) {
    $query->where('fulldate', '!=', $event->fulldate);
})

Update: To get player of no calendar entry

->whereHas('calendars', function ($query) use ($event) {
    $query->where('fulldate', '!=', $event->fulldate)
          ->orWhere('fulldate', null);
})

Upvotes: 1

Related Questions