Laravel: whereHas->WhereHas->Where query. Need to add wherePivot

Question

I am selecting all the ScheduledPrograms from a certain data range that has Attendees that belong to a certain User. I want to add a filter to select only the SchduledPrograms where the pivot field registered=1

I.E. I need to add a wherePivot('registered', 1) for the many-to-many relation scheduledPrograms->attendees. I How do I do this? My mind is scrambled from all the where clauses.

$programs = ScheduledProgram::where('registration_start_date', '<=', $today)
              ->where('end_date', '>=',  $today)
              ->whereHas('attendees', function($q) use($user)
              {
                  $q->whereHas('user', function($q) use($user){
                      $q->where('id', $user->id);
                  });
               })->get();

Models

Attendee->belongsTo('User')   
        ->belongsToMany('ScheduledPrograms')

User->hasMany('Attendee')

ScheduledProgram->belongsToMany('Attendee')

` ScheduledProgram Model

  public function attendees()
  {
    return $this->belongsToMany('Attendee', 'prog_bookings')->withPivot('registered','paid');
  }

Answer 1

wherePivot() can be used only for belongsToMany instances, but whereHas() closure function receives Builder instance instead.

So you can't use wherePivot() inside whereHas().

Answer 2

Method wherePivot works with model instances, not with the Model Query Builder.

I think when using whereHas method, the pivot table is already joined by eloquent, so just start using it in the whereHas method:

$programs = ScheduledProgram::where('registration_start_date', '<=', $today)
          ->where('end_date', '>=',  $today)
          ->whereHas('attendees', function($q) use($user)
          {
              $q
                  //========= See this: =========
                  ->where('prog_bookings.registered', '1')
                  ->whereHas('user', function($q) use($user){
                      $q->where('id', $user->id);
                  });
           })
           ->wherePivot('registered', 1)
           ->get();