I recently discovered "Pivot" classes in Laravel and was wondering if there is a way to use a Pivot class's relations in an Eloquent Query Builder.

Let's assume we have the followings:

• User, Subject and Offer Model (where subject is Math, Sciences, etc. And offer is a purchasable item that contains courses)
• UserSubject Pivot class (user_id, subject_id, offer_id, with BelongsTo relationships)

At this point, we assume all our Models have implemented the correct relationships using the Pivot class.

I now would like to check if a User has a specific subject for a given offer's type (Offer::TYPE_ONLINE_COURSE for example):

$user->hasSubject(Subject::MATH, Offer::TYPE_ONLINE)

1. Can pivot relationship be eager loaded ? (pivot.offer, for example)

2. Is it possible to use pivot's class relationship in a Query Builder:

User::query()
    ->whereHas('subject', fn(Builder $query) => $query
        ->where('name', $subjectName)
        ->whereRelation('pivot.offer', 'type', $offerType)
    )
    ->get();

CodePudding user response：

Can pivot relationship be eager loaded ? (pivot.offer, for example)

All relationships can be eager loaded, so the short answer is: Yes

Is it possible to use pivot's class relationship in a Query Builder:

Yes, but you need to use different relationships. Lets look at the basic relationship first:

User model:

//Relation to all subjects belonging to user
function subjects() {
    return $this->belongsToMany(Subject::class, 'user_subjects');
}

Subject model:

//Relation to all users belonging to subject
function users() {
    return $this->belongsToMany(User::class, 'user_subjects');
}

As you might have noticed here, you can get users from a subject and subjects from a user. You might actually use withPivot() to get the value of offer_id, but not the actual model.

You can add additional relationships to make this possible:

User model:

function userSubjects() {
    return $this->hasMany(UserSubject::class);
}

UserSubject model:

function user() {
    return $this->belongsTo(User::class);
}
function subject() {
    return $this->belongsTo(Subject::class);
}
function offer() {
    return $this->belongsTo(Offer::class);
}

Subject model:

function userSubjects() {
    return $this->hasMany(UserSubject::class);
}

Offer model:

function userSubjects() {
    return $this->hasMany(UserSubject::class);
}

With the last addition, you can load everything you want:

User::whereHas('userSubject', function($userSubjectQuery) use($offerType, $subjectName) {
    $userSubjectQuery
        ->whereHas('subject', function($subjectQuery) use($subjectName) {
            $subjectQuery->where('name', $subjectName);
        })
        ->whereHas('offer', function($offerQuery) use($offerType) {
            $offerQuery->where('type', $offerType); 
    });
});

The second set of whereHas is executed in perspective of the UserSubject model, which now has a queryable offer relationship.