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?

*"What I did wrong?"* - You specified `'id'` as both the Primary Key and Foreign Key (3rd and 4th argument) of `belongsToMany()`; one of those should probably be `'invite_id'`. I forget which one, but your `invite_riskarea_riskfield` does not have an `id` column. — Tim Lewis, Mar 17 '22 at 17:08
And the `InviteRiskareaRiskfield` model seems to be the pivot model, so the definition itself is wrong — IGP, Mar 17 '22 at 17:26
@IGP which relationship do you suggest to sync a list of `invite_riskarea_riskfield` with the `sync` method? — sfarzoso, Mar 17 '22 at 17:33
sync is only available for belongstomany relationships, so you'd need a relationship between invite <-> riskarea_riskfield (using invite_riskarea_riskfield as a pivot) — IGP, Mar 17 '22 at 17:46

IGP · Accepted Answer · 2022-03-17T17:49:12.057

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]
    );

if you have some free time could you please take a look at this https://stackoverflow.com/questions/71518270/how-to-access-to-pivot-data-using-multiple-condition — sfarzoso, Mar 17 '22 at 19:52

Cannot use belongsToMany to sync pivot data

1 Answers1