How to get one to many relations to a pivot-CodePudding

I have 2 models Scheme & Sponsor with a many-to-many relationship with pivot SchemeSponsor. The pivot table has a one-to-many relationship with ContributionRate. I want to get all sponsors related to a scheme together with all contributionrates.

Scheme

id
name

Sponsor

id
name

SchemeSponsor

id
sponsor_id
scheme_id
pivot_data

ContributionRate

id
scheme_sponsor_id
rate

In the Sponsor model, I have this relationship

    public function contribution_rates()
{
    return $this->hasManyThrough(
        ContributionRates::class,
        SchemeSponsor::class,
        'sponsor_id',
        'scheme_sponsor_id',
        'id',
        'id'
    );
}

This relationship returns all contributionrates even where the Scheme - Sponsor relationship does not exist. I want the rates that are related to the Pivot table SchemeSponsor. How can I achieve this via eager loading?

CodePudding user response：

Seems like you are wanting to do a has-many-through-many-to-many which isn't supported by Laravel

You can, however, use eloquent-has-many-deep from jonas-staudenm (which makes your life much easier)

e.i. on Your Scheme Model

use \Staudenmeir\EloquentHasManyDeep\HasRelationships;

public function sponsors() {
    return $this->belongsToMany(Sponsor::class);
}

public function contributionRate() {
    return $this->hasManyDeep(
        ContributionRate::class,
        ['scheme_sponsor', Sponsor::class], // Pivot tables/models starting from the Parent
        [null, null, 'scheme_sponsor_id']
    );
}

CodePudding user response：

I have used EagerLoadPivotTrait by Arjon Jason Castro to eager load pivot relations.

Scheme Model

class Scheme extends Model
{
    return $this->belongsToMany(Sponsor::class)
        ->using(SchemeSponsor::class)
        ->withPivot(
            'pivot_data',
        );
}

Sponsor Model

use AjCastro\EagerLoadPivotRelations\EagerLoadPivotTrait;

class Sponsor extends Model
{
    use EagerLoadPivotTrait;

    return $this->belongsToMany(Scheme::class, 'scheme_sponsor')
        ->withPivot(
            'pivot_data',
        );
     }

Then, to get the Scheme together with Sponsors and Sponsors' contribution rates;

($scheme
        ->with('sponsors.pivot.contribution_rates')
        ->where('id', $scheme->id)->get()->toArray())[0]

I have noted in as much as $scheme is a single model, eagerloading sponsors with contribution rates returns all schemes with the sponsors plus the contribution rates.