Defining Many-To-Many Relationships and Pivot Columns
In this lesson, we'll learn how you can define many-to-many relationships within your Lucid ORM Models. We'll also discuss the relationship options, and how you can automatically include pivot table data with all queries.
- Author
- Tom Gobich
- Published
- Apr 02
- Duration
- 9m 48s
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
Defining Many-To-Many Relationships and Pivot Columns
-
[MUSIC]
-
All right, so we already have some form of relationship between our movies and
-
our Cineasts via our writer and director IDs.
-
Both of these point directly to our Cineasts IDs, and we have that relationship
-
mapped for each as a one to many or many to one,
-
depending on what side of the relationship you're looking at it on.
-
However, things get a little bit interesting because we also get to
-
introduce pivot tables, crew movies, cast movies,
-
which are in charge of binding individual cast members.
-
And individual crew members via our movies and cynist tables as well.
-
And that's where the many to many relationship type enters into the frame.
-
In terms for actual models,
-
we're not going to have a model specifically for the pivot tables.
-
Instead, we're going to use the relationship bindings to define what
-
the pivot tables are, and any additional intermediary data columns that we
-
might want to pluck along with that relationship.
-
In terms of our tables, that's going to be their title or
-
their character name, in addition to the sort order.
-
So defining a many to many relationships relatively similar to the others.
-
We just have a decorator and a type that informs Lucid what the relationship is and
-
what type it is.
-
So if we scroll down a little bit here to give us some extra room and
-
type @many2many, there we go.
-
We can auto import that by hitting tab.
-
Now for the model that we return to the callback function here,
-
we want to return back the model for
-
the exact opposite side of the many to many relationship.
-
So for that, we would want to go from our movies,
-
which is the model we're in right now, through either our crew movies or
-
cast movies pivot tables into our underlying cynist table.
-
Which then means that we would want to reach and return back our cynist model,
-
as that is the model for the exact opposite side of this many to many relationship.
-
Then we can declare, right now, let's just go ahead and call this cynist.
-
We're going to have two because we have one for our crew movies and
-
one for our cast movies as well.
-
So we'll take care of individualizing those once we get this fully defined.
-
Then we'll have our type of many to many.
-
We can hit tab to auto import that as well.
-
Type of our cynist model.
-
All right, so if you're thinking ahead,
-
the way that we can discern what intermediary table this relationship
-
should use, as well as the pivot columns that we want to plot with it,
-
is by providing additional column options for our many to many relationship.
-
Similarly to how we're doing with our writer and
-
our director in our one to many relationship.
-
However, the options for
-
the many to many are drastically different than all of our other relationship types.
-
So instead of just foreign key, we now have two different options.
-
So if we type out foreign key or just foreign here,
-
we're going to see autocomplete options for pivot foreign key and
-
pivot related foreign key.
-
If we bring back up our diagram here,
-
remember we're on the movies model at present.
-
So we'll be referring to things as though we're coming from this side of
-
the relationship.
-
Our pivot foreign key is going to be a pivot tables key column
-
pointing back to the model that we're presently on.
-
So since we're on our movies model, the pivot foreign key would be our movie ID
-
on either our crew movies or cast movies pivot tables.
-
So let's do pivot foreign key movie_id.
-
Since we don't have models for our pivot tables to refer off of,
-
we need to provide the column name as it's defined in the database for
-
these foreign key values.
-
And in our database, we're using snake case.
-
So we'll want to provide it as snake case for
-
this particular relationship definition.
-
Then that means our pivot related foreign key is going to be
-
the key column on the pivot table or the related model.
-
Since we're on our movies model at present,
-
the related model is going to be our cynist model.
-
And the pivot related foreign key then would be our cynist_id column
-
on either one of our two pivot tables.
-
So for our pivot related foreign key, we can provide cynist_id just like so.
-
There are two other columns that we can relate to as well.
-
We can specify both of the ID columns for the parent model,
-
which would be the movie model which we're presently on, and
-
the related model as well, which would be our cynist model.
-
So for those, that would be our local key for the parent ID column,
-
which would be our movies ID column, and then related key,
-
which would be the ID column on our cynist model.
-
Now, if you're following along so far, and we stop for a second here and
-
compare what we have to what's defined inside of our database, you'll see, okay,
-
the two IDs match up one to one, so we can go off the default for
-
both our local key and related key for these.
-
Our pivot foreign key is movie_id.
-
On both of our models, we are using movie_id for the naming for
-
our crew movies as well as our cast movies.
-
So for both of those, we can use the default for our pivot foreign key as well.
-
And that's also true for our pivot related foreign key,
-
which is using cynist_id for both of these relations as well.
-
In turn, meaning we're okay to use the defaults for
-
all of what we've defined so far.
-
So we can go ahead and get rid of them to remove that clutter.
-
However, we have strayed pretty darn far from Lucid's default naming convention for
-
our pivot table.
-
By default, Lucid's going to concatenate together the two model names for
-
the relationship, which would be our movie and cynist models with an underscore.
-
So it's gonna make them snake case.
-
So it'll be movies_cynists, and then it's going to sort them.
-
So instead of movie_cynist, it would be cynist_movie.
-
In our case, if we bring our relationship back up,
-
we have two variations of this relationship.
-
So we have crew movies, and we have cast movies.
-
So not only will we not be using the default because we've also pluralized
-
these names, we have crew_movies rather than just crew movie.
-
But we've also deviated from our cynist model name so
-
that we can have two different variations of this relationship.
-
So for both of these, we will need to define pivot table option.
-
And this is how we're going to dictate which pivot table we're using for
-
the actual relationship definition and
-
allow us to have two different versions of this relationship between our cynist and
-
our movies.
-
So our first pivot table, we're gonna call our crew movies.
-
And we can declare the actual property name however we'd like.
-
So we could just call this crew if we wanted to.
-
However, I would like to have it pluralized so
-
that we know just by the name that it is an array.
-
So let's call this crew members.
-
Let's give this a copy and a paste because we also need a representation of
-
this relationship for our cast movies pivot table as well.
-
And the name for this one, we can call our cast members.
-
Everything else for these two relationships will remain the same.
-
Okay, so before we move any further, let's go ahead and
-
re-reference our pivot tables as we have them defined inside of pgAdmin.
-
So we have our crew movies pivot table right here.
-
We can take a look at the columns for this.
-
And in addition to our two relationship columns, our cynist_id and
-
movie_id, we have additional information provided on this table,
-
like our title and sort order.
-
And if you care about them, they're created at an updated.
-
I believe we have the exact same setup on our cast movies as well.
-
So if we take a look here, yep, it looks relatively similar.
-
So we have our two relation columns.
-
And then instead of title, this is character name.
-
And then we also have sort order that we added in later on down here.
-
Okay, so let's hide pgAdmin back away.
-
For those additional columns that we have defined on our pivot table,
-
we can either eagerly define on our model that we always want that information
-
included whenever we load in the relationship.
-
Or we can lazily load that in anytime that we specifically need it
-
from the query builder.
-
So that we can cover both instances of that, let's define it eagerly for
-
just one, and then we'll cover how to lazily load it in the other.
-
So on our cast members, let's go ahead and define a pivot columns option.
-
For this option, all that we need to do is provide an array of the actual column
-
names, and AdonisJS will provide them anytime that we load in
-
this particular relationship.
-
So our cast members was the one that had, I believe,
-
character name as its information column, and then it also had a sort order.
-
Now, although we don't have models for our many to many relationships,
-
we can actually have Lucid still maintain our created at and
-
updated at timestamps automatically for us.
-
So let's go ahead and open up our browser and jump into the documentation.
-
Right here, I'm at lucid.adonisjs.com, and we've gone down to the model section,
-
relationships, many to many, specifically the pivot table timestamps section.
-
There is a column here called pivot timestamps that we can set the true.
-
When we set this to true, Lucid is going to automatically set and
-
update the timestamps on insert or update appropriately for our timestamps.
-
It will also automatically convert them to a Lux and
-
date time as we have on our other models when we're working with date time values.
-
Additionally, had you named those two columns anything different,
-
you can specify those two columns by providing an object into the pivot
-
timestamps with the created at column name provided as well as the updated at
-
column name.
-
And if you just want one or the other, you can provide false for
-
whichever one you don't want.
-
So for both of our relationships here, I think it would be great to go ahead and
-
have Lucid automatically take care of that for us.
-
So let's go ahead and set pivot timestamps to true for both of these.
-
Jump down to here, pivot timestamps to true like so.
-
So now anytime that we insert through our model relationship for our cast or
-
crew members, those pivot timestamps will automatically be added or
-
updated appropriately for the action that we're taking.
-
So that's officially one side of our many to many relationship defined.
-
The other side just needs to pretty much mirror the relationship definition.
-
So if we copy what we have here, jump over to our Sinus model,
-
scroll down to where we have our relationships defined for
-
our movies written and movies directed.
-
Underneath there, we can go ahead and paste this in.
-
We'll get red squigglies because our many to many is not imported.
-
We can just retype that, hit tab to auto import and retype this one and
-
hit tab to auto import that as well to take care of that.
-
And since we're now on our Sinus model,
-
we no longer want to relate this to our Sinus model, but rather our movie model.
-
So we'll want to copy that and paste that in for
-
the model for both of these relationships as well.
-
And then lastly, we may or may not want to update the name.
-
Whenever we load this in from our Sinus side,
-
we probably want to refer to it as their crew movies and
-
cast movies or something similar to that.
-
So I think that's good enough for this use case.
-
So we'll go ahead and do crew movies here, just like so, and
-
cast movies for this one, just like so.
-
Introduction
-
Fundamentals
-
2.0Routes and How To Create Them5m 23s
-
2.1Rendering a View for a Route6m 29s
-
2.2Linking Between Routes7m 51s
-
2.3Loading A Movie Using Route Parameters9m 17s
-
2.4Validating Route Parameters6m 6s
-
2.5Vite and Our Assets6m 38s
-
2.6Setting Up Tailwind CSS9m 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 7s
-
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
-
The Flow of Middleware7m 49s
-
Authenticating A Newly Registered User4m 14s
-
Checking For and Populating an Authenticated User2m 10s
-
Logging Out An Authenticated User2m 24s
-
Logging In An Existing User6m 54s
-
Remembering A User's Authenticated Session6m 55s
-
Protecting Routes with Auth, Guest, and Admin Middleware5m 36s
-
-
Filtering and Paginating Queries
Join The Discussion! (2 Comments)
Please sign in or sign up for free to join in on the dicussion.
bruce-irakoze
Hello @tomgobich ,
I am currently working with multi-level hierarchical data. I have identified a pattern called
Closure Table
(https://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html), which appears to be a great fit for my requirements. Now, I want to model this using Lucid, and this is where I'm facing some challenges. Below are the table structures:For example, here is how we can insert a node into the referrals table:
Could you please help me understand how I can model this structure using Lucid and provide an example of how to translate the above query using Lucid?
Thank you.
Please sign in or sign up for free to reply
tomgobich
Hi Bruce!
I'm going to preface this by saying, I'm not familiar with the closure table approach, though I did give the link a read-through.
That said, I'm not sure exactly how well this will translate to Lucid. You'll likely need to perform the actual insert as a raw query, and at that point, it may be easier to just define the entire thing as a raw query as well.
However, something like the below might work, at least for the select portion, if you're looking to keep with the query builder.
I hope this helps!
Please sign in or sign up for free to reply