An Alternative Approach to Many-To-Many Relationships
In this lesson, learn an alternative approach to many-to-many relationships that allows you to work directly with the pivot table as a model.
- Author
- Tom Gobich
- Published
- May 10
- Duration
- 4m 56s
Developer, dog lover, and burrito eater. Currently teaching AdonisJS, a fully featured NodeJS framework, and running Adocasts where I post new lessons weekly. Professionally, I work with JavaScript, .Net C#, and SQL Server.
Adocasts
Burlington, KY
Transcript
An Alternative Approach to Many-To-Many Relationships
-
(upbeat music)
-
So far within our database schema,
-
we've worked with everything except for our watchlist table.
-
Our watchlist is bound to our users and our movies table,
-
and it's gonna keep track of movies
-
an individual user is interested in watching.
-
So our user ID and movie ID there
-
keeps track of that relationship,
-
and it will be a many to many relationship
-
because we can have many users
-
wanting to watch many movies,
-
and many movies with many users wanting to watch them.
-
Apart from those two columns,
-
we have our standard ID created at and updated at columns,
-
but we also have this watched at timestamp as well.
-
And we're gonna use this to allow a user
-
to keep track of whether or not they've watched a movie,
-
and we're keeping that as a timestamp
-
just in case we might ever be interested
-
in showing when exactly they watched it.
-
So if this does not have a value,
-
then it means that they didn't watch it,
-
and if it does have a time within there,
-
then it means that they watched it.
-
Now, unlike with our crew movie and cast movie tables
-
where we didn't have a model for either one of those,
-
and instead we worked directly
-
through the many to many relationship
-
between the movie and Cineasts table.
-
For our watch list, we are going to create a model for it,
-
despite it being a many to many relationship.
-
And then within the model itself,
-
we'll define the relationship
-
between the movies and the watch list,
-
and the watch list and the users individually
-
without actually using that many to many type.
-
This is a great approach whenever your pivot table
-
is the focal point of the relationship,
-
where we can work directly with a watch list model
-
rather than working through the many to many relationship
-
on either side of that.
-
And it also gives us a chance to show an alternative way
-
to define a many to many relationship as well.
-
So let's do node ace make,
-
and we'll create a model specifically for our watch list.
-
We'll also make a migration and a controller
-
using hyphen MC there, run that.
-
Okay, we can clear that out, jump into our text editor,
-
and let's first dive into our database,
-
migrations, create watch list table migration.
-
So everything by default here is a okay.
-
In addition to our ID,
-
we're gonna want a table integer for our user ID.
-
This will be unsigned references,
-
our users.id to create that foreign key relationship
-
between our watch list and our user.
-
This will be not nullable and on delete,
-
we'll want to cascade the deletion
-
into any of these records.
-
We can go ahead and give this line a copy
-
because we're gonna want that to be relatively similar
-
for our movie ID there as well.
-
The only difference is going to be instead of users,
-
it's going to be for our movies.
-
Lastly, we have one additional timestamp,
-
our table timestamp watched.
-
All right, that should do it for our migration.
-
We can go ahead and jump back into our terminal
-
and run node ace migration run there.
-
Okey-doke, clear that out.
-
NPM run dev to go ahead and put our server backup,
-
hide that away.
-
And now let's jump into our watch list model
-
and get the columns defined here as well.
-
So we have an at column, declare.
-
We have our user ID of type number.
-
We also have a column for our movie ID.
-
That too is of type number.
-
And then we have the watched at.
-
So we'll do at column.
-
We'll get that as a date time if it exists.
-
And then we'll declare that as watched at
-
as a type of date time or null since it is optional.
-
Now, since we have a model specifically
-
for this pivot table of our watch list,
-
instead of using the many to many relationship type
-
in either our user or our movie model,
-
instead we'll want to create this
-
as a one to many relationship.
-
Meaning within our watch list table,
-
this will be belongs to for both our user,
-
declare user belongs to type of user.
-
Scroll down a little bit further here
-
and we'll do the exact same thing for our movie.
-
So belongs to movie, declare movie,
-
belongs to type of movie.
-
And that reads quickly there, of course,
-
is just because we need to add an import type
-
for belongs to.
-
So that's the relationship within our actual watch list model.
-
What about within our user and movie model?
-
Well, within our user, let's go ahead and scroll on down
-
to where we have our relationships.
-
And rather than a has one or belongs to on this side,
-
it's just gonna be a has many
-
and we'll bind it directly to our watch list model.
-
So we'll do at has many there
-
and point it to our watch list model.
-
Declare watch lists as many type of watch list.
-
Now watch lists may not be the most appropriate name
-
because in display sense,
-
the user is really just gonna have one list,
-
it's not multiple lists.
-
So in that case, maybe we will just name this watch list
-
rather than watch lists,
-
despite it being a has many relationship,
-
just to stifle any potential confusion there.
-
Then on our movie side,
-
we're gonna do pretty much the exact same thing.
-
So we'll just need to scroll
-
to where we have our relationships.
-
We'll do these above the many to many
-
in between our belongs to here.
-
And actually we can go ahead
-
and just copy this verbatim from our users
-
and go back into our movie, paste it in.
-
And we just need to hit command dot there
-
to get our quick fix options
-
and add in our missing imports.
-
So command dot there, command dot here.
-
There's also an add all missing imports.
-
So I suppose we could go ahead and click that there as well.
-
Give that a save.
-
And now we have our relationships bound.
-
And despite our watch list being an actual pivot table
-
between our users and movies,
-
we've instead defined it as a one to many relationship
-
between our movies and watch lists
-
and a one to many relationship
-
between our users and watch lists,
-
which gives us an alternative view
-
at how we can go about many to many relationships
-
within our application.
-
Structurally, it's all still just the same thing.
-
It's just how we're treating it that's different.
-
Introduction
-
Fundamentals
-
2.0Routes and How To Create Them5m 24s
-
2.1Rendering a View for a Route6m 30s
-
2.2Linking Between Routes7m 52s
-
2.3Loading A Movie Using Route Parameters9m 18s
-
2.4Validating Route Parameters6m 7s
-
2.5Vite and Our Assets6m 39s
-
2.6Setting Up Tailwind CSS7m 5s
-
2.7Reading and Supporting Markdown Content4m 32s
-
2.8Listing Movies from their Markdown Files8m 51s
-
2.9Extracting Reusable Code with Services7m 4s
-
2.10Cleaning Up Routes with Controllers4m 52s
-
2.11Defining A Structure for our Movie using Models9m 38s
-
2.12Singleton Services and the Idea of Caching6m 11s
-
2.13Environment Variables and their Validation4m 16s
-
2.14Improved Caching with Redis10m 44s
-
2.15Deleting Items and Flushing our Redis Cache6m 46s
-
2.16Quick Start Apps with Custom Starter Kits6m 28s
-
2.17Easy Imports with NodeJS Subpath Imports8m 40s
-
-
Building Views with EdgeJS
-
3.0EdgeJS Templating Basics8m 49s
-
3.1HTML Attribute and Class Utilities6m 9s
-
3.2Making A Reusable Movie Card Component10m 24s
-
3.3Component Tags, State, and Props4m 53s
-
3.4Use Slots To Make A Button Component6m 56s
-
3.5Extracting A Layout Component5m 13s
-
3.6State vs Share Data Flow2m 59s
-
3.7Share vs Global Data Flow6m 8s
-
3.8Form Basics and CSRF Protection6m 13s
-
3.9HTTP Method Spoofing HTML Forms3m 3s
-
3.10Easy SVG Icons with Edge Iconify7m 57s
-
-
Database and Lucid ORM Basics
-
4.0Configuring Lucid and our Database Connection4m 3s
-
4.1Understanding our Database Schema9m 35s
-
4.2Introducing and Defining Database Migrations18m 35s
-
4.3The Flow of Migrations8m 28s
-
4.4Introducing Lucid Models5m 43s
-
4.5Defining Our Models6m 49s
-
4.6The Basics of CRUD11m 56s
-
4.7Defining Required Data with Seeders11m 11s
-
4.8Stubbing Fake Data with Model Factories13m 48s
-
4.9Querying Our Movies with the Query Builder15m 30s
-
4.10Unmapped and Computed Model Properties3m 24s
-
4.11Altering Tables with Migrations7m 6s
-
4.12Adding A Profile Model, Migration, Factory, and Controller2m 57s
-
4.13SQL Parameters and Injection Protection9m 19s
-
4.14Reusable Query Statements with Model Query Scopes8m 11s
-
4.15Tapping into Model Factory States9m 15s
-
4.16Querying Recently Released and Coming Soon Movies4m 59s
-
4.17Generating A Unique Movie Slug With Model Hooks7m 59s
-
-
Lucid ORM Relationships
-
5.0Defining One to One Relationships Within Lucid Models5m 49s
-
5.1Model Factory Relationships2m 54s
-
5.2Querying Relationships and Eager Vs Lazy Loading5m 17s
-
5.3Cascading and Deleting Model Relationships5m 16s
-
5.4Defining One to Many Relationships with Lucid Models6m 56s
-
5.5Seeding Movies with One to Many Model Factory Relationships5m 24s
-
5.6Listing A Director's Movies with Relationship Existence Queries8m 41s
-
5.7Listing and Counting a Writer's Movies8m 41s
-
5.8Using Eager and Lazy Loading to Load A Movie's Writer and Director5m 18s
-
5.9Defining Many-To-Many Relationships and Pivot Columns9m 48s
-
5.10Many-To-Many Model Factory Relationships4m 50s
-
5.11A Deep Dive Into Relationship CRUD with Models18m 5s
-
5.12How To Create Factory Relationships from a Pool of Data13m 55s
-
5.13How To Query, Sort, and Filter by Pivot Table Data9m 47s
-
-
Working With Forms
-
6.0Accepting Form Data12m 15s
-
6.1Validating Form Data with VineJS9m 29s
-
6.2Displaying Validation Errors and Validating from our Request7m 16s
-
6.3Reusing Old Form Values After A Validation Error2m 3s
-
6.4Creating An EdgeJS Form Input Component5m 28s
-
6.5Creating A Login Form and Validator5m 1s
-
6.6How To Create A Custom VineJS Validation Rule9m 7s
-
-
Authentication & Middleware
-
7.0The Flow of Middleware7m 49s
-
7.1Authenticating A Newly Registered User4m 14s
-
7.2Checking For and Populating an Authenticated User2m 10s
-
7.3Logging Out An Authenticated User2m 24s
-
7.4Logging In An Existing User6m 54s
-
7.5Remembering A User's Authenticated Session6m 55s
-
7.6Protecting Routes with Auth, Guest, and Admin Middleware5m 36s
-
-
Filtering and Paginating Queries
-
8.0Creating A Movie List Page3m 43s
-
8.1Filtering A Query By Pattern Likeness7m 9s
-
8.2Filtering Our List by Movie Status5m 47s
-
8.3How To Apply A Dynamic Sort Filter To Your Query7m 12s
-
8.4Joining SQL Tables To Order By A Related Column4m 49s
-
8.5Validating Query String Filter Values7m 24s
-
8.6How To Paginate Filtered Query Results9m 15s
-
8.7Pagination First, Last, Next, and Previous Buttons4m 3s
-
-
User Watchlist
-
9.0An Alternative Approach to Many-To-Many Relationships4m 56s
-
9.1Toggling A Movie in an Authenticated User's Watchlist9m 56s
-
9.2Listing and Filtering User Watchlist Items7m 31s
-
9.3Allowing Users To Toggle A Movie As Watched4m 44s
-
9.4Filtering By User's Watched Status6m 7s
-
9.5Defining A Composite Unique Constraint4m 47s
-
9.6Persist Filters Easily with Lucid's Query String Method3m 58s
-
-
User Profiles
-
10.0How to Create and Fix Missing User Profiles in Your Application7m 37s
-
10.1Using Dependency Injection to Update A User's Profile9m 46s
-
10.2Saving All Or Nothing with Database Transactions5m 15s
-
10.3Uploading and Displaying User Avatars15m 29s
-
10.4Displaying A User's Profile6m 1s
-
10.5Filtering, Preloading, and Sorting By Relationship7m 6s
-
-
Admin Panel
-
11.0Creating An Admin Layout7m 14s
-
11.1Counting Stats for our Admin Dashboard5m 43s
-
11.2Paginated Admin Movie Table13m 2s
-
11.3Allowing Admins to Create Movies16m 39s
-
11.4Allowing Admins to Update Movies and Clear Values13m 27s
-
11.5How To Use One Form to Create or Edit Movies5m 32s
-
Uploading Movie Cover Images in our Create or Edit Form10m 29s
-
Using A Wildcard Route Param to Download Storage Images7m 57s
-
Posting Objects, Arrays, and an Array of Objects in HTML Forms26m 26s
-
Managed Transactions and Syncing Movie Cast Members15m 55s
-
Allowing Admins to Delete Movies and their Relationships7m 42s
-
Thank You for Watching!0m 31s
-
Join The Discussion! (0 Comments)
Please sign in or sign up for free to join in on the dicussion.
Be the first to Comment!