Many to Many relation with unique records in pivot

Published 3 years ago by francois

Hi, When you use attach() method in a many to many relation, you can add this record multiple times.

Here is an example of the pivot table

id, model1, model2

1, 10, 11

2, 10, 11

And, I'd like to forbidden this behavior.

Sync() method can't be used in my case. Thanks.

Best Answer (As Selected By francois)
stidges

When the combination should be unique, you can remove the 'id' column from the pivot table. An 'id' column is only necessary if other fields in your table can't make up a unique value. Try removing the id column and making the primary key of your table a combination of model1 and model2

MattMangoni

If you don't want multiple records, why are you using a many to many relationship to begin with? :)

francois

I just want unique attachment.

For example : A user can have a newsletter subscription on multiple kink of newsletter (type). And a newsletter (type) can have many users registered. So, I need to be sure that a user hasn't subscribe several times to a newsletter.

(it's just an example)

stidges

When the combination should be unique, you can remove the 'id' column from the pivot table. An 'id' column is only necessary if other fields in your table can't make up a unique value. Try removing the id column and making the primary key of your table a combination of model1 and model2

francois

@stidges : Thanks !

So, if I use this : https://github.com/JeffreyWay/Laravel-4-Generators#pivot I should update the migration file to this ? :


class CreateOrderUserTable extends Migration { /** * Run the migrations. * * @return void */ public function up() { Schema::create('order_user', function(Blueprint $table) { $table->integer('order_id')->unsigned()->index(); $table->foreign('order_id')->references('id')->on('orders')->onDelete('cascade'); $table->integer('user_id')->unsigned()->index(); $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade'); $table->timestamps(); }); } /** * Reverse the migrations. * * @return void */ public function down() { Schema::drop('order_user'); } }
stidges

Yep! To really enforce the unique constraint, you could also add this:

$table->primary(['order_id', 'user_id']);
francois

Ok. So it's the couple order_id + user_id that will be unique ?

stidges

Yeah, a primary key MUST be unique, so if you place that on those two columns, the combination has to be unique.

francois

Many thanks Stidges

GiantCowFilms

Now when a duplicate insert occurs I get a SQL error and the app crashes. How should I prevent this.

eldringoki

@GiantCowFilms

Filter user requests. Make use of "validators" to validate user inputs and apply various useful rules to it like uniqueness to a certain table and require a certain field to be numeric and such. You can also customize error messages returned from failed validations. Just make sure you control what inputs goes into your database.

pmall
pmall
1 year ago (579,045 XP)

Just use sync instead of attach

samfrjn11

For anyone else bumping on this thread:

->sync([userID], false) the falseparameter means sync without detaching.

Since L5.1 you can also use syncWithoutDetaching

Sign In or create a forum account to participate in this discussion.