-
So currently with the way that we have things, there's nothing actually preventing a single user from having a single movie inside of their watch list
-
more than once, which may result in unexpected behavior in terms of our application. To run through a quick example of this, if we jump into a REPL session, so let's clear this out,
-
node ace REPL, await, load models. Inside of our application, we have our user John Doe with an email of test@test.com. And we also have this movie,
-
I'm your boogeyman with an ID of 420 that is currently in this user's watch list. So we should have one result for that. Let's go ahead and grab our user.
-
So const user equals await models.user.find, buy, or fail. And we'll search by the email, test@test.com.
-
Let's also go ahead and grab our movie as well. So movie equals await models, movie, find, or fail, the ID of 420, verify that that's the right movie. There we go.
-
So currently if we were to do await user related watch list query where movie ID is 420, and let's also add Poyo to that
-
just to get all of the extra lucid properties out of the way. And we ran that. This movie's only in this user's watch list once, but there's nothing preventing us
-
from actually doing await models watch list, create user ID of our user.ID and movie ID of movie.ID.
-
Essentially adding this movie into this user's watch list again. So if we run this, there's all of the model properties.
-
But if we go up two times and rerun our same query, we can verify that this single movie is inside of the single user's watch list now twice, which in terms of our application,
-
if we were to exit out of a REPL session and boot that back up, jump back into our browser, is gonna result in at least a little bit of unexpected behavior because we're just querying
-
the first watch list and deleting it whenever we're removing an item from a user's watch list. So if we click on this, it's gonna appear as though nothing had happened because we have now that second record
-
that's showing up making this still in that user's watch list. If we give this a click once more, now it's actually out of that user's watch list and both of those records are now deleted. If we give it a click once more,
-
things start to work more naturally as we expect them to. So what we wanna do is go into our database structure
-
and enforce that the user ID and movie ID combination should only be inside of this table a single time.
-
And we can do that using a unique constraint. Now we know that we can apply a unique constraint on a single property by adding in unique, but we need to apply it as a combination
-
of both of these columns. So adding it that way is not gonna work. Instead, what we wanna do is table. And there is a unique method that we can call here
-
that accepts in an array of column names. So inside of this array, we're gonna want to specify both of the columns that we want to be combined unique,
-
which is our user ID and our movie ID. And with this defined, now this table will only allow one combination
-
of a single user ID and movie ID pair, meaning our user with an ID of seven can now only have the movie with an ID of 420 inside of the database once.
-
And it will fail if we attempt to add a second record in. Now the argument could be made that we would also want that to be true for our cast movies and crew movies tables.
-
However, the inverse argument can be made as well. We may want to allow multiple sinists bound to a single movie so that they can have multiple characters
-
that they may play in that single movie. And the same with crew with their job titles. So for these, there's some fluidity there. And I think for that reason, we leave those as is.
-
And we just leave the unique constraint here specifically for our watch list. Now with our unique constraint applied, what we need to do is roll back to undo the single watch list migration.
-
So node, ace, migration, rollback. Okay, that reverted our previous batch, which just include our create watch list table migration.
-
And then we just need to node, ace, migration, run to reapply that batch of migrations. And now our watch list table has that unique constraint applied.
-
If we jump back into our browser, refresh. Oop, gotta reboot our server real quick. NPM run dev. There we go. Now let's refresh. Add this movie back into our watch list. There we go.
-
And now if we jump back into our terminal, let's stop our server, clear that out. Node, ace, REPL. Await, load models.
-
And attempt to do awaits models.watchlist.create. User ID of seven, which is our John Doe user.
-
And movie ID of 420, which is the movie that we just applied into our watch list. If we attempt to create a second record with these same IDs, we're gonna get an error
-
because that now violates our unique constraint that we've added into this table. Awesome, so everything there is working A-okay. And we've now prevented the possibility
-
of a user having the same movie inside of their watchlist more than once.
