How to query over relationship that is inside intermediate many-to-many model (pivot)?

Question

I am trying to make Eloquent query that goes inside custom intermediate model and filters the results by relationship inside that intermediate table model.

Let me describe the structure and show what I have tried:

Models:

InfluencerProfileInfo extends Model
{
    public function socialMedias()
    {
        return $this->belongsToMany(SocialMedia::class)
            ->withPivot('id','influencer_social_media_type_id', 'url', 'followers')
            ->using(InfluencerSocialMedia::class);
    }
}

------------------------
|id   name    age  ... |
|                      |
------------------------

SocialMedia extends Model
{
    public function influencerProfileInfos()
    {
        return $this->belongsToMany(InfluencerProfileInfo::class)
            ->withPivot('id','influencer_social_media_type_id', 'url', 'followers')
            ->using(InfluencerSocialMedia::class);
    }
}

------------
|id   name |
|          |
------------

InfluencerSocialMedia extends Pivot
{
    public function influencerProfileInfo()
    {
        return $this->belongsTo(InfluencerProfileInfo::class, 'influencer_profile_info_id', 'id');
    }

    public function socialMedia()
    {
        return $this->belongsTo(SocialMedia::class, 'social_media_id', 'id');
    }

    public function reachedAuditories()
    {
        return $this->hasMany(InfluencerSocialMediaReachedAuditory::class, 'influencer_social_media_id', 'id');
    }
}

-------------------------------------------------------------------------
|id   influencer_profile_info_id    social_media_id    url    followers |
|                                                                       |
-------------------------------------------------------------------------

InfluencerSocialMediaReachedAuditory extends Model
{
    public function influencerSocialMedia()
    {
        return $this->belongsTo(InfluencerSocialMedia::class, 'influencer_social_media_id', 'id');
    }

    public function auditory()
    {
        return $this->belongsTo(Auditory::class, 'auditory_id', 'id');
    }
}


---------------------------------------------------------------
|id   influencer_social_media_id    auditory_id   percent(int)|
|                                                             |
---------------------------------------------------------------

I want to take only the profiles that have socialMedias and on that socialMedias they have reachedAuditory percent over 10 and also the auditory_id is 2

I have tried:

$profiles = InfluencerProfileInfo::whereHas('socialMedias', function ($query) {
     // On this line I got only profileInfos that have at least one socialMedia witch is correct

     // $query->where('followers', '>', 222000); I can search on column that is on the pivot

     $query->whereHas('reachedAuditories', function ($q) { // this row gives error: Call to undefined method App\Models\SocialMedia::reachedAuditories()
                $q->where('auditory_id', 2)->where('percent', '>', 10);
            });
      })->get();

I am able to do this, so its not problem with the relationship declaration:

$profiles[0]->socialMedias[0]->pivot->reachedAuditories()->where('auditory_id', $auditoryId)->get();

$infSocialMedia = new InfluencerSocialMedia();
$infSocialMedia->reachedAuditories()->create(['influencer_social_media_id' => $infSocialMedia->id, 'auditory_id' => 4, 'percent' => 47]);

Answer 1

If you create a hasMany relationship to the pivot and use the whereHas() through there. I would think you can achieve your desired functionality.

InfluencerProfileInfo extends Model
{
    public function influencerSocialMedia() {
        return $this->hasMany(InfluencerSocialMedia::class, 'influencer_profile_info_id');
    }
}

Now nest your whereHas() and query the reachedAuditories.

InfluencerProfileInfo::whereHas('influencerSocialMedia.reachedAuditories', function($query) {
    $query->where('auditory_id', 2)->where('percent', '>', 10);
});