Cannot use belongsToMany to sync pivot data

Question

I have a pivot table called invite_riskarea_riskfield which defined a relationship with the invite table:

What I need to do is sync multiple invite_riskarea_riskfield permissions (insert, edit, view). So I tried to set the following relationship in the Invite model:

public function permissions()
{
    return $this->belongsToMany(
        InviteRiskareaRiskfield::class,
        'invites',
        'id',
        'id'
    );
}

So I should be able to do $invite->permissions()->sync($permissions);

But the relationship returns this error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'invite_riskarea_riskfield.id' in 'on clause' (SQL: select invite_riskarea_riskfield.*, invites.id as pivot_id from invite_riskarea_riskfield inner join invites on invite_riskarea_riskfield.id = invites.id where invites.id = 17)

What I did wrong?

Answer 1

Assuming your models are:

• Invite (invites table)
• RiskAreaRiskField (riskarea_riskfield table)
• InviteRiskareaRiskfield (invite_riskarea_riskfield table).

Your relationship should be the following. Feel free to change the method names to something that makes more sense to you.

class Invite extends Model
{
    public function invite_riskarea_riskfield()
    {
        return $this->hasMany(InviteRiskAreaRiskField::class, 'invite_id');
    }

    public function riskarea_riskfield()
    {
        return $this->belongsToMany(RiskAreaRiskField::class, 'invite_riskarea_riskfield', 'invite_id', 'riskarea_riskfield_id')
                    ->withPivot(['insert', 'edit', 'view'])
                    ->using(InviteRiskAreaRiskField::class);
    }
}

class RiskAreaRiskField extends Model
{
    public function invite_riskarea_riskfield()
    {
        return $this->hasMany(InviteRiskAreaRiskField::class, 'riskarea_riskfield_id');
    }

    public function invite()
    {
        return $this->belongsToMany(Invite::class, 'invite_riskarea_riskfield', 'riskarea_riskfield_id', 'invite_id')
                    ->withPivot(['insert', 'edit', 'view'])
                    ->using(InviteRiskAreaRiskField::class);
    }
}

use Illuminate\Database\Eloquent\Relations\Pivot;

class InviteRiskareaRiskfield extends Pivot
{
    public function invite()
    {
        return $this->belongsTo(Invite::class, 'invite_id');
    }

    public function riskarea_riskfield()
    {
        return $this->belongsTo(RiskAreaRiskField::class, 'riskarea_riskfield_id');
    }
}

$invite = Invite::find(/* some id */);

// sync with riskarea_riskfield id 1, 2 and 3
$invite->riskarea_riskfield() // the belongsToMany relationship
    ->sync([
        1, // default values for insert, edit, view
        2 => ['insert' => 432, 'edit' => 13, 'view' => 542],
        3 => ['insert' => 654, 'edit' => 777, 'view' => 222]
    ]);


// sync with riskarea_riskfield id 4, 5 and 6 with the same permissions
$invite->riskarea_riskfield() // the belongsToMany relationship
    ->syncWithPivotValues(
        [4, 5, 6],
        ['insert' => 654, 'edit' => 777, 'view' => 222]
    );