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

#javascript #sql #beginners

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.

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
  },
});

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
  },
});

"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,
    }
  },
});

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

Books.belongsToMany(Authors, { through:  Writings});
Authors.belongsToMany(Books, { through: Writings });

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
    }
})

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);

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,
    }
  },
});

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

Books.hasMany(Readings);
Readings.belongsTo(Books);
Authors.hasMany(Readings);
Readings.belongsTo(Authors);

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
    }
})

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
    }
  ]})

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
      }
     }
    }
  })