CODEWITHSUNDEEP
phplaravellaravel-4eloquent
halkujabra
halkujabra

Reputation: 2942

Updating Laravel ManyToMany Relationship without deleting(or detaching)

Goal - Update many-to-many relationship but not to delete rows

Suppose, User & Role models have many-to-many relationship. A particular $user has three roles - [1,2,3]. It means there are three rows in the pivot table for this $user.

Now, I want to update the relationship. I want the roles now to be [1,2,4]. I don't want to delete the rows on pivot table (because this will lead to a gap in the IDs of pivot table). If I use sync([1,2,4]), it will delete the row corresponding to role '3'. If I use sync([1,2,4],false), it will keep the row with role '3'. I want the role '3' to be updated to '4' without any deletion. How can this be achieved?

I am aware of detach() function but that again deletes the rows.

EDIT: ORIGIN OF THE QUESTION

If I have a many-to-many relationship between User & Role models & often the roles for a user keep changing. So, is it better to keep detaching old roles & attaching new roles? Or, I should update the role_id on existing rows of the pivot table without deleting/detaching existing rows?

I am concerned about this because if I delete a row & add another one, the 'id' gets incremented & there is a gap in place of the previous 'id'. If I have millions of users & thousands of roles, wouldn't the 'id' getting bigger be causing a problem to me in future?

If it exceeds integer limits, I know it will not exceed bigInt limit(most probably). That's fine. But I read somewhere, as the IDs get bigger, the performance gets lower.

Can someone please tell me the best practice for this?

Upvotes: 1

Views: 814

Answers (1)

lukasgeiter
lukasgeiter

Reputation: 152890

I wouldn't worry about deleting and adding records in your pivot table.
Regarding the size of your IDs, the maximum value of an unsigned INT in MySQL is 4'294'967'295. I don't believe you will reach that very soon. If you still are concerned you can:

  1. Use a unsigned BIGINT (max 18446744073709551615)
  2. Just remove the ID entirely, it's often not needed in pivot tables.

Upvotes: 3

Related Questions