Belongs-To-Many associations are used to connect sources with multiple targets.
Furthermore the targets can also have connections to multiple sources.
Project.belongsToMany(User, { through: 'UserProject' });
User.belongsToMany(Project, { through: 'UserProject' });
This will create a new model called UserProject with the equivalent foreign keys projectId and userId.
Whether the attributes are camelcase or not depends on the two models joined by the table (in this case, User and Project).
Defining the through property is required.
Sequelize would previously attempt to autogenerate names, but that would not always lead to the most logical setups.
This will add methods to instances of the Project class such as: getUsers, setUsers, addUser, addUsers.
This will also add methods to instances of the User class such as: getProjects, setProjects, addProject, and addProjects.
Some types of associations within databases include but are not limited to: one-to-one, one-to-many, and many-to-many.
A many-to-many relationship occurs when multiple records in a table are associated with multiple records in another table.
Put another way, a many-to-many relationship refers to a relationship between tables in a database when a parent row in one table contains several child rows in the second table, and vice-versa.
With PostgreSQL as our object-relational database management system (ORDBMS) and Sequelize as our library of choice to implement object-relational mapping (ORM), we will be covering the bases when it comes to Sequelize's belongsToMany() method. Having previously explored hasOne(), hasMany(), and belongsTo(), we will now elaborate on belongsToMany() in order to broaden our familiarity and deepen our understanding of associations via Sequelize.
When it comes to many-to-many (M:N or M2M) associations and their complements, a few real-world examples come to mind:
- When it comes to literature and publishing, an author can write many books, and a book can be written by many authors
- When it comes to institutions of education and scheduling, a student can register for many classes, and a class can include many students
- When it comes to baking and cooking, an ingredient can be used in multiple recipes, and a recipe can require multiple ingredients
- When it comes to medicine and healthcare, a doctor can see many patients, and a patient can see many doctors
- When it comes to commerce and retail, a customer can purchase many different products, and a product can be purchased by many different customers
- When it comes to an online store that sells clothing, one article of clothing can be a part of many different orders, and one order can contain many different articles of clothing
- When it comes to threads and forums, a user can subscribe to many different forums, and a forum can have many different users subscribed to it
The purpose of this section is to primarily and ultimately get us to start thinking about how different sets of data can be associated, particular in a many-to-many setup.
This section specifically aims to get us to ask and answer the root questions of: how does Entity X relate to Entity Y? How does Entity Y relate to Entity X?
These general examples of various many-to-many associations set us up nicely for the following section where we'll dig deeper into more specific examples that utilize Sequelize's belongsToMany() method.
Link: https://github.com/ajLapid718/Affiliated
Here's the scenario. In our database so far, we have a few tables. Our tables include: players, coaches, teams. We will be inserting another table, a table of trainers.
This is the situation. Basketball players can have many trainers. A basketball player can have a basketball trainer who helps them develop their shooting form. A basketball player can also have a different basketball trainer who helps them develop their ability to pass the basketball. A basketball player can also have a basketball trainer dedicated to increasing their endurance and agility. By the same token, a basketball trainer can train many different basketball players in general.
Due to the reality and inherent nature of the relationship between a basketball player and a basketball trainer, we can be inclined to setting up a many-to-many association so that we can properly represent the fact that a basketball player can train with many different basketball trainers, and that a basketball trainer can train many different basketball players.
Note: Keeping in mind how this relationship differs from that of the relationship between a head coach and a basketball team might help bring clarity on this many-to-many association at hand.
Establishing this type of association allows us to: get all of the players trained by a particular trainer or get all of the trainers who train a particular player. What is the significance of this relationship-dependent information in practice? The former could allow us to see who trains together during the off-season and could also allow us to see the size of a trainer's clientele. The latter provides us with information about which trainers are guiding the development of a particular basketball player, and could potentially allow for someone to make suggestions regarding which trainers should or should not train that particular basketball player.
Although we have a table of coaches and a table of teams, we'll solely be focusing on the players table and the trainers table.
Before going on any further, let's see how the tables look when they are populated in order to have more concrete information and details.
Now that we have some tangible data to refer to, we can take a more thorough look at what can be done via a many-to-many association.
Let's say that our objective, at the moment, is to be able to get a list of all of the players who are trained by a particular trainer.
One way to think of this is that we need to add a column titled "trainerId" to the table of players, which would allow us to identify and group together all players who have the same trainerId. This would work, but only if every player had one and only one trainer. Since this is inherently a many-to-many relationship and not a one-to-many or one-to-one relationship, we have to look at this from a different angle.
We need a way to group trainerIds and playerIds in such a way where numerous trainerIds can be tied to numerous playerIds.
To drive the point home, imagine we had a table (tableWeNeedToMake) where one column was titled "trainerId" and another column on that table was titled "playerId".
If basketball trainer Tyler Relph had a trainerId of 1, and basketball players Kyrie Irving, Marcus Morris, and Stephen Curry respectively had playerIds of 1, 7, and 22. Tyler Relph trains Kyrie Irving, Marcus Morris, and Stephen Curry.
Let's also say that basketball trainer Jordan Lawley has a trainerId of 2, and basketball player LeBron James has a playerId of 27. Jordan Lawley trains Lebron James AND Kyrie Irving.
If we populated tableWeNeedToMake based of the information above, we'd have five rows in total.
In the first row of tableWeNeedToMake, we'd see a trainerId of 1 and a playerId of 1. This indicates that Tyler Relph trains Kyrie Irving.
In the second row of tableWeNeedToMake, we'd see a trainerId of 1 and a playerId of 7. This indicates that Tyler Relph trains Marcus Morris.
In the third row of tableWeNeedToMake, we'd see a trainerId of 1 and a playerId of 22. This indicates that Tyler Relph trains Stephen Curry.
In the fourth row of tableWeNeedToMake, we'd see a trainerId of 2 and a playerId of 27. This indicates that Jordan Lawley trains LeBron James.
In the fifth row of tableWeNeedToMake, we'd see a trainerId of 2 and a playerId of 1. This indicates that Jordan Lawley trains Kyrie Irving.
As shown in the bold text, this many-to-many association allows us to represent the fact that two basketball trainers (aka, more than one basketball trainer) can train many players and that a player can be trained by more than one basketball trainer. There is no numerical ceiling here, which makes a many-to-many relationship different from implementing hasOne(), hasMany(), or belongsTo().
Let's finally set up this many-to-many association (in the file: database/models/index.js).
Our (relevant) associations, so far, appear like so (see preceding GitHub gist or the path above for other associations):
// Signature: Source.association(Target);
Trainer.belongsToMany(Player, { through: 'TrainersPlayers' }); // A many-to-many relationship that generates a JOIN table called TrainersPlayers, which contains a column titled trainerId and a column titled playerId;
Player.belongsToMany(Trainer, { through: 'TrainersPlayers' }); // A many-to-many relationship that generates a JOIN table called TrainersPlayers, which contains a column titled trainerId and a column titled playerId;After adding those two lines of code, our players tables and trainers tables now appear like so (unchanged):
Wait, so nothing appears to have happened even though we associated both tables? Hmm.
So, here's where the important stuff happens. Here's our new join/through/pivot/junction table called "TrainersPlayers", which contains a column for "trainerId" and a column for "playerId":
Almost there. So what just happened?
We declared:
- A trainer belongs to many players
- A player belongs to many trainers
What did this provide for us?
- We generated a join/through/pivot/junction table, which groups trainerIds and playerIds
How did this happen?
- As far as the Sequelize-given instance method belongsToMany() is concerned, we are able to construct and arrange foreign keys (trainerId and playerId, in the example above) and position them in the appropriate location (within the TrainersPlayers table).
- In the example of Trainer.belongsToMany(Player):
- The source is the Trainer model
- The target is the Player model
- With an invocation of belongsToMany(), Sequelize creates the junction table with the sourceId (trainerId) and targetId (playerId)
- In the example of Player.belongsToMany(Trainer):
- The source is the Player model
- The target is the Trainer model
- With an invocation of belongsToMany(), Sequelize creates the junction table with the sourceId (trainerId) and targetId (playerId)
All of that to say: through the many-to-many associations, we carefully and purposefully grouped together and housed the foreign keys of trainerIds and playerIds in one, organized location so that we can see who trains with who and who is trained by whom.
Is there anything else to keep in mind?
- Associations are responsible for much more than generating foreign keys and altering tables
- Associations are also responsible for adding certain methods on instances of the models
- The first belongsToMany() call provides the following methods for the source model, instances of our Trainer model in this case:
- Trainer.getPlayers;
- Trainer.countPlayers;
- Trainer.hasPlayer;
- Trainer.hasPlayers;
- Trainer.setPlayers;
- Trainer.addPlayer;
- Trainer.addPlayers;
- Trainer.removePlayer;
- Trainer.removePlayers;
- Trainer.createPlayer;
- The second belongsToMany() call provides the following methods for the source model, instances of our Player model in this case:
- Player.getTrainers;
- Player.countTrainers;
- Player.hasTrainer;
- Player.hasTrainers;
- Player.setTrainers;
- Player.addTrainer;
- Player.addTrainers;
- Player.removeTrainer;
- Player.removeTrainers;
- Player.createTrainer;
For this reason, in addition to not polluting both master tables (Trainer and Player) with foreign keys, an association like belongsToMany() is a significant piece of database relationships.
Because of this, we can now query the database in order to get a list of all of the players who are trained by a particular trainer.
The route and query look like so (this can be found in: routes/trainers.js):
// GET http://localhost:1234/api/trainers/:id/players;
// We want to get a list of all of the players who are trained by a particular trainer;
router.get('/:id/players', async (req, res, next) => {
let foundTrainer;
try {
foundTrainer = await Trainer.findOne({ where: { id: req.params.id } });
}
catch (err) {
next(err);
}
let playersOfTrainer;
try {
playersOfTrainer = await foundTrainer.getPlayers(); // remember those methods Sequelize provides?;
}
catch (err) {
next(err);
}
res.status(200).json(playersOfTrainer);
});The raw output looks like so:
There you have it! For all intents and purposes, we have retrieved a list of all of the basketball players who are trained by Tyler Relph! In other words, with the GET request to that endpoint in the screenshot above, we received a response of an array containing instances of the Player class if that instance contained a trainerId of 1, which corresponds to Tyler Relph's trainerId. Reminder: the TrainersPlayers table allowed for this data to successfully come together.
Based off of that, let's try to set another objective. Let's say that our new objective is to be able to get all of the basketball trainers who train a particular basketball player.
Almost there! We can now query the database to achieve our new objective.
The route and query look like so (this can be found in: routes/players.js):
// GET http://localhost:1234/api/players/:id/trainers;
// We want to get a list of all of the trainers who train a particular player;
router.get('/:id/trainers', async (req, res, next) => {
let foundPlayer;
try {
foundPlayer = await Player.findOne({ where: { id: req.params.id } });
}
catch (err) {
next(err);
}
let trainersOfPlayer;
try {
trainersOfPlayer = await foundPlayer.getTrainers(); // remember those methods Sequelize provides?;
}
catch (err) {
next(err);
}
res.status(200).json(trainersOfPlayer);
});The prettified output looks like so:
Player 30 is:
Therefore, based on the response we received earlier, we now know Semi Ojeleye is trained by Tyler Relph and Jordan Lawley.
Some players, like Semi Ojeleye, have many trainers. Other players have only one trainer at the moment. This is perfectly acceptable within the bounds of a many-to-many relationship.
For all intents and purposes, we have retrieved a list of all of the trainers who train Semi Ojeleye!
That's that.
Note: Many-to-Many relationships can be tricky to grasp, but reading the documentation, troubleshooting via Stack Overflow, and experimenting with Postman can be quite insightful. This article aimed to build up a familiarity with what happens when belongsToMany() is invoked. This article did not cover other possibilities such as: adding other columns to the join table (such as a column indicating the date the trainer and player first began their working relationship or a column indicating which skill the trainer and basketball player are focusing on), querying those properties, adding associations to the JOIN table, aliasing, etc.
- Thanks to associations, we were able to grab certain relationship-dependent information
- We went over belongsToMany();
- The belongsToMany() method provides the following methods for the source model:
- Source.getTargets;
- Source.countTargets;
- Source.hasTarget;
- Source.hasTargets;
- Source.setTargets;
- Source.addTarget;
- Source.addTargets;
- Source.removeTarget;
- Source.removeTargets;
- Source.createTarget;
- We discussed the nature and appearance of a join/through/pivot/junction table