php - Laravel - filter relation data inside relation

Question

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:

not every player has an event in calendar (so I cannot access to fulldate attr)
player hasMany calendars, so it is returned as another collection, I have to run ->whereNotIn(... in every item of this calendars collection...

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

@NiRR - just ordinary datetime, for example 2017-11-10 16:00:00 — Nidecker
– Nidecker, Commented Nov 7, 2017 at 17:47

NiRR · Accepted Answer · 2017-11-28 08:34:14Z

1

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

edited Nov 28, 2017 at 8:34

answered Nov 7, 2017 at 17:41

NiRR

5,0728 gold badges35 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nidecker Over a year ago

Perfect! It works, thank you, just small typo - calendars not calendar :)

Abid Raza · Accepted Answer · 2017-11-07 18:03:15Z

1

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

edited Nov 7, 2017 at 18:03

answered Nov 7, 2017 at 17:25

Abid Raza

7558 silver badges15 bronze badges

7 Comments

Nidecker Over a year ago

Thanks, now that date check works, but players without calendars are not included

Abid Raza Over a year ago

Great! Happy to help. Accept the answer if it helped.

Nidecker Over a year ago

Well I need players without calendar to be included too :)

Abid Raza Over a year ago

Updated answer for players without calendar.

Nidecker Over a year ago

This will return only players WITH calendar, if player doesn't have calendar, there is no fulldate property, whole calendar object is empty. NiRR's solution works

|

Collectives™ on Stack Overflow

php - Laravel - filter relation data inside relation

2 Answers 2

1 Comment

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related