DEV Community

loading...
Cover image for Sequelize's "Super Many-to-Many" Associations

Sequelize's "Super Many-to-Many" Associations

MichaelPaulKunz
Computer science student in New Orleans, LA
Updated on ・4 min read

A common way to explain many-to-many models in relational databases is the book-author relationship. An author can write many books. And a book can be co-written by two or more authors. If we draw lines connecting a collection of books to their authors, we'd have multiple lines matching some books to many authors and most authors (J.D. Salinger not included) to their many books.

Alt Text

If we map this in Sequelize, our schema for books looks like this:

const Books = sequelize.define('Books', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    allowNull: false,
    autoIncrement: true,
    unique: true,
  },
  title: {
    type: DataTypes.STRING,
    allowNull: false
  },
});
Enter fullscreen mode Exit fullscreen mode

And our schema for authors looks like this:

const Authors = sequelize.define('Authors', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    allowNull: false,
    autoIncrement: true,
    unique: true,
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false
  },
});
Enter fullscreen mode Exit fullscreen mode

"Books" doesn't have an "Authors" key, and "Authors" doesn't have a "Books" key. Their relationship is defined in a separate join table that references the book and author IDs.

const Writings = sequelize.define('Writings', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    allowNull: false,
    autoIncrement: true,
    unique: true,
  },
  BookId: {
    type: DataTypes.INTEGER,
    references: {
      model: Users,
      key: Users.id,
    }
  },
  AuthorId: {
    type: DataTypes.INTEGER,
    references: {
      model: Merchants,
      key: Merchants.id,
    }
  },
});
Enter fullscreen mode Exit fullscreen mode

In Sequelize, we define many-to-many relationships with "belongsToMany" and "through."

Books.belongsToMany(Authors, { through:  Writings});
Authors.belongsToMany(Books, { through: Writings });
Enter fullscreen mode Exit fullscreen mode

Once this is all tied up in our schemas, and we've filled out our tables, we can query the Authors table, including the books they've written, with the following command:

Authors.findAll({
    where: {},
    include: {
      model: Writings,
      include: Books
    }
})
Enter fullscreen mode Exit fullscreen mode

This will give us all of our authors, including an array of the books they've written under the key "Books." This is all fine and good if there is only one relationship between authors and books. But authors can also READ books. What if we were mapping a database that collected authors, the books they've written, and also the books they've read? Sequelize's usual many-to-many model doesn't cover this situation, since the joined table will come back as its original table name (Books) instead of its join table name (Writings). This is a job for Sequelize's Super Many-to-Many Association schema.

Whereas "belongsToMany" is used to map many-to-many relationships, "hasMany" and "belongsTo" map one-to-many relationships. Instead of directly linking books and authors with belongsToMany, we can link "Books" and "Writings" with a "hasMany" command, and also link "Authors" and "Writings" the same way. That code looks like this:

Books.hasMany(Writings);
Writings.belongsTo(Books);
Authors.hasMany(Writings);
Writings.belongsTo(Authors);
Enter fullscreen mode Exit fullscreen mode

We never explicitly bind Books to Authors, but once we've done this, we can run the same query command from earlier, and the array of books each author has written will come back under the key "Writings" instead of the key "Books." This means that we can declare another join table called "Readings" and map out a totally separate many-to-many relationship.

const Readings = sequelize.define('Readings', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    allowNull: false,
    autoIncrement: true,
    unique: true,
  },
  BookId: {
    type: DataTypes.INTEGER,
    references: {
      model: Users,
      key: Users.id,
    }
  },
  AuthorId: {
    type: DataTypes.INTEGER,
    references: {
      model: Merchants,
      key: Merchants.id,
    }
  },
});
Enter fullscreen mode Exit fullscreen mode

We do everything we did for Writings, but with Readings instead:

Books.hasMany(Readings);
Readings.belongsTo(Books);
Authors.hasMany(Readings);
Readings.belongsTo(Authors);
Enter fullscreen mode Exit fullscreen mode

And likewise change "Writings" to "Readings" in our earlier query to get back a list of authors with the books they've read:

Authors.findAll({
    where: {},
    include: {
      model: Readings,
      include: Books
    }
})
Enter fullscreen mode Exit fullscreen mode

I'm working on a project where I needed to plot multiple many-to-many relationships between tables, and the Sequelize docs only ever got me half the way there. They explain how to implement the "super many-to-many" association, but they don't really explain what it's useful for. So I had to follow a hunch that maybe it was the thing I needed. And then I had to search for old requests in help forums to get the right syntax for the calls.

You can reference multiple join tables in your query by defining your includes key as an array. So you could query the Authors table and have it include both the books they've written and the books they've read with this command:

Authors.findAll({
    where: {email: email},
    include:
    [{
      model: Writings,
      include: Books,
    },
    {
      model: Readings,
      include: Books
    }
  ]})
Enter fullscreen mode Exit fullscreen mode

You can also nest "include" fields. So if you wanted to get back a list of authors, including the books they've read, and have each book include its own authors in its own list, you could write this:

Authors.findAll({
    where: {},
    include:
    {model: Readings,
      include: {
        model: Books,
        include: {
          model: Writings,
          include: Authors
      }
     }
    }
  })
Enter fullscreen mode Exit fullscreen mode

Helpful links:

Wikipedia Many-To-Many

Sequelize Docs

Writers Who Only Wrote One Novel

Discussion (0)