# 3.3.2: Sequelize Many-To-Many (M-M) Relationships ## Learning Objectives 1. Sequelize provides relationship methods to query for data related through M-M relationships 2. Understand how to set up models and migrations to support M-M relationships with Sequelize 3. Understand how to query data related through M-M relationships with Sequelize 4. Understand how to add and query for data in junction tables for M-M relationships ## Introduction Similar to 1-M relationships, Sequelize provides relationship methods for M-M relationships that make it easy to query related data. Using our `Person` and `Personality` example from the SQL M-M submodule where each person can have many personalities and vice versa, Sequelize allows us to call methods such as `person.getPersonalities()` and `personality.getPeople()` to retrieve related data. The following is Sequelize's official introduction to M-M relationships with Sequelize. {% embed url="" %} Official guide to Sequelize Associations: Many-To-Many relationships {% endembed %} 1. Note the concept of the junction table is central to many to many relationships 2. In Sequelize's example their junction table is called `actor_movies`, but it could also have been called `movie_actors` if they wanted. The convention is to name the junction table with both model names concatenated, where the 1st model name is singular and the 2nd is plural. It does not matter which model is 1st and which is 2nd. 3. Note that we declare an M-M relationship between 2 models with the `belongsToMany` association method, where we specify the junction table name in a 2nd parameter with a `through` attribute 4. We will need to create the junction table `actor_movies` using a migration because we are not using Sequelize `sync` 5. There is no need to define the `actor_movies` junction model ourselves unless we plan to include non-foreign-key data in that model. However, we will always need to create the `actor_movies` table with a migration because Sequelize will not create it for us. ## Models and Migrations for M-M relationships with Sequelize We will continue with the `People` and `Personalities` examples from the SQL M-M Relationships submodule. ### When to define new model for junction table If the 2 tables we are associating with an M-M relationship do not need to store non-foreign-key data in their junction table, we do not need to create a standalone model for the junction table in our app. However, we will always need to use a migration to create the junction table in our database. In this section let us assume there is no non-foreign-key data in the junction table and focus on model association methods and the migration for the junction table. ### Model association methods for M-M relationships Given `Person` and `Personality` models, we apply the `belongsToMany` association method on each of them toward the other to set up an M-M relationship in Sequelize. Like in the `Actor` and `Movie` example in the Sequelize official docs above, we declare the through table name `person_personalities` in the `belongsToMany` method. This also means we need to create a `person_personalities` table in our database using migrations. {% code title="db/models/person.js" %} ```javascript "use strict"; const { Model } = require("sequelize"); module.exports = (sequelize, DataTypes) => { class Person extends Model { /** * Helper method for defining associations. * This method is not a part of Sequelize lifecycle. * The `models/index` file will call this method automatically. */ static associate(models) { this.belongsToMany(models.personality, { through: "person_personalities" }); } } Person.init( { name: DataTypes.STRING, }, { sequelize, modelName: "person", underscored: true, } ); return Person; }; ``` {% endcode %} {% code title="db/models/personality.js" %} ```javascript "use strict"; const { Model } = require("sequelize"); module.exports = (sequelize, DataTypes) => { class Personality extends Model { /** * Helper method for defining associations. * This method is not a part of Sequelize lifecycle. * The `models/index` file will call this method automatically. */ static associate(models) { this.belongsToMany(models.person, { through: "person_personalities" }); } } Personality.init( { trait: DataTypes.STRING, }, { sequelize, modelName: "personality", underscored: true, } ); return Personality; }; ``` {% endcode %} ### Junction table migration for M-M relationships Now that we've set up the M-M association between `Person` and `Personality` models, let's create a migration to create the junction table `person_personalities` in our database. The following migration assumes we have past migrations that have already created `people` and `personalities` tables. {% code title="20220531155826-create-personpersonality.js" %} ```javascript "use strict"; module.exports = { async up(queryInterface, Sequelize) { await queryInterface.createTable("person_personalities", { id: { allowNull: false, autoIncrement: true, primaryKey: true, type: Sequelize.INTEGER, }, person_id: { type: Sequelize.INTEGER, references: { model: "people", key: "id", }, }, personality_id: { type: Sequelize.INTEGER, references: { model: "personalities", key: "id", }, }, created_at: { allowNull: false, type: Sequelize.DATE, }, updated_at: { allowNull: false, type: Sequelize.DATE, }, }); }, async down(queryInterface, Sequelize) { await queryInterface.dropTable("person_personalities"); }, }; ``` {% endcode %} Notice there are 2 foreign keys in the `person_personalities` junction table, 1 referencing the `people` table and the other referencing the `personalities` table. Even though the key in the `references` object is `model`, Sequelize expects the value to be a plural table name. Just like that, we've set up an M-M association in our app and are ready to start using Sequelize relationships methods on this relationship! ## Query M-M Associated Tables in Sequelize In the Sequelize 1-M Relationships submodule we read about "special methods/mixins added to instances" and ignored the `belongsToMany` section. Let's go back and read the `belongsToMany` section to understand what relationship methods Sequelize provides to models associated with `belongsToMany`. {% embed url="" %} Official guide to Sequelize Associations: Special methods/mixins added to instances (`belongsToMany`) {% endembed %} 1. Probably the most common relationship method we will use is `fooInstance.getBars()`, simply getting all the instances of the related table associated with the current instance. For example, `person.getPersonalities()`. 2. No need to worry about retrieving join table attributes in this section. We will explore that in the "Handling non-foreign-key data in junction table" section below. 3. Note the large number of relationship methods we can call on model instances with a M-M relationship, including methods to count the number of related model instances, check existence of related model instances, set, add, remove, create related model instances. We will likely not need to use all of these, but good to keep in mind in case using them can simplify our code logic! 4. We can generally assume that Sequelize will get the plural form of our model names right. For example, we can assume that the Sequelize relationship method to get personalities associated with a `Person` instance will be `Person.getPersonalities()` and not something like `Person.getPersonalitys()`. ## Handling non-foreign-key data in junction table When we wish to store and retrieve non-foreign-key data in our junction table and access that data from our apps, we will need to explicitly create a new model in our apps for the junction model. Once we've created the junction model and junction table migration we can discuss how to query the junction model in our apps. ### Models and migrations for junction tables with non-foreign-key data #### Models We must explicitly define the junction table model if we wish to query non-foreign-key data in the junction table. We name the model the singular of the table name, i.e. since the table name was `person_personalities`, the model is `PersonPersonality`. In the model is the `intensity` attribute that accompanies each person's personality trait. We also include an `id` attribute, typically omitted in models that we will include here to enable querying our junction table (more on this below). We also include the foreign keys to `People` and `Personalities` tables respectively. Notice there are 2 `belongsTo` association methods to `Person` and `Personality` models respectively. Recall `belongsTo` associations are typically used with 1-M relationships. We included these because we want to declare 1-M associations between both `Person` and `PersonPersonality` and between `Personality` and `PersonPersonality`. We will explore why we add these 1-M relationships in the next section. {% code title="db/models/personpersonality.js" %} ```javascript "use strict"; const { Model } = require("sequelize"); module.exports = (sequelize, DataTypes) => { class PersonPersonality extends Model { /** * Helper method for defining associations. * This method is not a part of Sequelize lifecycle. * The `models/index` file will call this method automatically. */ static associate(models) { // Define separate 1-M relationships with both Person and Personality models // to enable them to query junction model this.belongsTo(models.person); this.belongsTo(models.personality); } } PersonPersonality.init( { id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true, allowNull: false }, PersonId: { type: DataTypes.INTEGER, references: { model: "people", key: "id", }, }, PersonalityId: { type: DataTypes.INTEGER, references: { model: "personalities", key: "id", }, }, intensity: DataTypes.INTEGER, }, { sequelize, modelName: "personpersonality", underscored:true } ); return PersonPersonality; }; ``` {% endcode %} To complete the 1-M relationships between `Person` and `PersonPersonality` and `Personality` and `PersonPersonality`, we will need to add a `hasMany` association in both `Person` and `Personality` models. Below is the same code as before except with the new association. {% code title="db/models/person.js" %} ```javascript "use strict"; const { Model } = require("sequelize"); module.exports = (sequelize, DataTypes) => { class Person extends Model { /** * Helper method for defining associations. * This method is not a part of Sequelize lifecycle. * The `models/index` file will call this method automatically. */ static associate(models) { // belongsToMany is for the M-M association to query related Personality instances this.belongsToMany(models.personality, { through: models.personpersonality }); // hasMany is for the 1-M association to query junction model this.hasMany(models.personpersonality); } } Person.init( { name: DataTypes.STRING, }, { sequelize, modelName: "person", underscored: true } ); return Person; }; ``` {% endcode %} {% code title="db/models/personality.js" %} ```javascript "use strict"; const { Model } = require("sequelize"); module.exports = (sequelize, DataTypes) => { class Personality extends Model { /** * Helper method for defining associations. * This method is not a part of Sequelize lifecycle. * The `models/index` file will call this method automatically. */ static associate(models) { // belongsToMany is for the M-M association to query related Person instances this.belongsToMany(models.person, { through: models.personpersonality }); // hasMany is for the 1-M association to query junction model this.hasMany(models.personpersonality); } } Personality.init( { trait: DataTypes.STRING, }, { sequelize, modelName: "personality", underscored_true } ); return Personality; }; ``` {% endcode %} #### Migrations Reference the `person_personalities` migration above on this page. Add an `intensity` column to the `person_personalities` table with integer data type. ### Querying junction tables with non-foreign-key data Sequelize has a separate, comprehensive guide to M-M relationships that includes how to manage non-foreign-key data in junction tables. #### Defining junction model to access non-foreign-key junction table attributes {% embed url="" %} Official Sequelize guide: Advanced M:N Associations {% endembed %} 1. The content on calling `belongsToMany` association methods is the same as what we read above 2. Note: defining the junction model ourselves allows us to specify non-foreign-key junction model values when calling Sequelize M-M relationship methods such as `fooInstance.addBar()`, or in the example on this page, `user.addProfile()`. 3. No need to worry about composite unique key for now, assume `id` is still the primary key for the junction table #### Through table versus normal tables and the "Super Many-to-Many association" {% embed url="" %} Official Sequelize guide: Advanced M:N Associations - Super Many-to-Many association {% endembed %} 1. Having an `id` primary key on `PersonPersonalities` model is necessary for the "Super Many-to-Many relationship" that allows both `Person` and `Personality` instances to query both the junction table and the table associated via the M-M relationship. I.e., to allow `Person` to query both related `PersonPersonality` and `Personality` instances, vice versa for `Personality`. 2. Notice that the tables in the database look the same regardless of whether we declare 2 1-M relationships between `person` and `person_personality` and `personality` and `person_personality`, or 1 M-M relationship between `person` and `personality`. 3. The "Super Many-to-Many relationship" requires us to declare 1-M relationships between the 2 M-M tables and the junction table, and an M-M relationship between the 2 M-M tables. In our `person` and `personality` example, this means declaring 1-M relationships between `person` and `person_personality` and between `personality` and `person_personality`, and an M-M relationship between `person` and `personality`. 4. We can ignore everything below "Aliases and custom key names" in the Sequelize M:N Associations guide for now. Those concepts are more advanced and we can come back to them as reference if we need them. #### Querying the junction model With the "super M-M relationship" we can query our junction model in multiple ways. The cleanest way is to query using M-M relationship methods with the `joinTableAttributes` option. ```javascript person.getPersonalities({ joinTableAttributes: ['intensity'] }); ``` Alternatively we can use 1-M relationship methods to access the junction table, but that may require more acrobatics with `Person` and `Personality` IDs. ## Additional Resources 1. [React Select](https://react-select.com/home) is a React component library that provides an elegant UI for collecting 1-M and M-M relationship data.