Edit Page

Many-to-many

AKA "Has and Belongs To Many"

Overview

#

A many-to-many association states that one record can be associated with many other records and vice-versa. This type of relationship involves the creation of a join table to keep track of the many links between records. When Waterline detects that two models have collection attributes that point to each other through their via keys (see below), it will automatically build up a join table for you.

The via key

#

Because you may want a model to have multiple many-to-many associations on another model a via key is needed on the collection attribute. The via key indicates the related attribute on the other side of a many-to-many association.

Using the User and Pet example, let’s look at how to build a schema where a User may have many Pet records and a Pet may have multiple owners.

// myApp/api/models/User.js
// A user may have many pets
module.exports = {
  attributes: {
    firstName: {
      type: 'string'
    },
    lastName: {
      type: 'string'
    },

    // Add a reference to Pet
    pets: {
      collection: 'pet',
      via: 'owners'
    }
  }
};
// myApp/api/models/Pet.js
// A pet may have many owners
module.exports = {
  attributes: {
    breed: {
      type: 'string'
    },
    type: {
      type: 'string'
    },
    name: {
      type: 'string'
    },

    // Add a reference to User
    owners: {
      collection: 'user',
      via: 'pets'
    }
  }
};

To associate records together, the Model method .addToCollection() is used. This allows you to set the primary keys of the records that will be associated.

// To add a Pet to a user's `pets` collection where the User has an id of
// 10 and the Pet has an id of 300.
await User.addToCollection(10, 'pets', 300);

You can also add multiple pets at once:

await User.addToCollection(10, 'pets', [300, 301]);

Removing associations is just as easy using the .removeFromCollection() method. It works the same way as addToCollection:

// To remove a User from a pet's collection of owners where the User has an id of
// 10 and the Pet has an id of 300.
await Pet.removeFromCollection(300, 'owners', 10);

And you can remove multiple owners at once:

await Pet.removeFromCollection(300, 'owners', [10, 12]);

Note that adding or removing associated records from one side of a many-to-many relationship will automatically affect the other side. For example, adding records to the pets attribute of a User model record with .addToCollection() will immediately affect the owners attributes of the linked Pet records.

To return associated collections along with a record retrieved by .find() or .findOne(), use the .populate() method.

Dominance

#

In most cases, Sails will be able to create the join table for a many-to-many association without any input from you. However, if the two models in the association use different datastores, you may want to choose which one should contain the join table. You can do this by setting dominant: true on one of the associations in the relationship.

Consider the following models:

// User.js
module.exports = {
  datastore: 'ourMySQL',
  attributes: {
    email: 'string',
    wishlist: {
      collection: 'product',
      via: 'wishlistedBy'
    }
  }
};
// Product.js
module.exports = {
  datastore: 'ourRedis',
  attributes: {
    name: 'string',
    wishlistedBy: {
      collection: 'user',
      via: 'wishlist'
    }
  }
};

In this case, User and Product records exist in different databases. By default, Sails will arbitrarily choose one of the datastores (either ourMySQL or ourRedis) to contain the join table linking the wishlist attribute of User to the wishlistedBy attribut of Product. In order to force the join table to exist in the ourMySQL datastore, you would add dominant: true to the wishlist attribute definition. Conversely, adding dominant: true to the wishlistedBy attribute would cause the join table to be created in the ourRedis datastore.

Choosing a "dominant"
#

Several factors may influence your decision of where to create the join table:

Is something missing?

If you notice something we've missed or could be improved on, please follow this link and submit a pull request to the sails repo. Once we merge it, the changes will be reflected on the website the next time it is deployed.

Concepts