extra columns in model association sailsjs

Question

How can I have an extra column in postgres with sailsjs model association?

This is an example of my two models

// Users.js attribute
...
challenges: {
  collection: 'challenge',
  via: 'userChallenge'
}


// Challenge.js attribute
...
userChallenge: {
  collection: 'users',
  via: 'challenges'
}
...

With this I get the table association (many to many)

 id | challenge_userChallenge | users_challenges 

I need one or more extra columns like "active" or something like that

Thanks in advance

Answer 1

You should use through associations.

Many-to-Many through associations behave the same way as many-to-many associations with the exception of the join table being automatically created for you. In a Many-To-Many through assocation you define a model containing two fields that correspond to the two models you will be joining together. When defining an association you will add the through key to show that the model should be used rather than the automatic join table.

Let's take the Post and Tag models as an example. The Post has and belongs to many Tag and the Tag has and belongs to many Post. These two models will be joined via the PostTag model.

Our Post model:

/**
 * Post.js
 *
 * @description :: A model definition.  Represents a database table/collection/etc.
 * @docs        :: https://sailsjs.com/docs/concepts/models-and-orm/models
 */

module.exports = {

  tableName: 'posts',

  attributes: {

    name: {
      type: 'string',
      required: true
    },

    // Many to many: Post has and belongs to many Tag.
    tags: {
      collection: 'Tag',
      via: 'postId',
      through: 'PostTag'
    }

};

Our Tag model:

/**
 * Tag.js
 *
 * @description :: A model definition.  Represents a database table/collection/etc.
 * @docs        :: https://sailsjs.com/docs/concepts/models-and-orm/models
 */

module.exports = {

  tableName: 'tags',

  attributes: {

    name: {
      type: 'string',
      unique: true,
      required: true
    },

    // Many to many: Tag has and belongs to many Post.
    posts: {
      collection: 'Post',
      via: 'tagId',
      through: 'PostTag'
    }

  }

};

Our PostTag model (we're creating it manually, we don't want Sails.js to create it automatically):

/**
 * PostTag.js
 *
 * @description :: A model definition.  Represents a database table/collection/etc.
 * @docs        :: https://sailsjs.com/docs/concepts/models-and-orm/models
 */

module.exports = {

  tableName: 'posts_tags',

  attributes: {

    postId: {
      model: 'Post'
    },

    tagId: {
      model: 'Tag'
    }

  }

};

The PostTag model is actually the join table. In this model, you can define your extra fields.

Hope this helps.

Answer 2

While the answer by Vladyslav Turak is correct for Sails v1.0 and up, please note that Through Associations are NOT SUPPORTED in Sails 0.12.

To achieve the similar effect with Sails 0.12, you can use the following:

The Post model:

/**
 * Post.js
 *
 * @description :: A model definition.  Represents a database table/collection/etc.
 * @docs        :: https://sailsjs.com/docs/concepts/models-and-orm/models
 */

module.exports = {

  attributes: {

    name: {
      type: 'string',
      required: true
    },

    // Many to many: Post has and belongs to many PostTag.
    tags: {
      collection: 'PostTag',
      via: 'post'
    }

};

The Tag model:

/**
 * Tag.js
 *
 * @description :: A model definition.  Represents a database table/collection/etc.
 * @docs        :: https://sailsjs.com/docs/concepts/models-and-orm/models
 */

module.exports = {

  attributes: {

    name: {
      type: 'string',
      unique: true,
      required: true
    },

    // Many to many: Tag has and belongs to many PostTag.
    posts: {
      collection: 'PostTag',
      via: 'tag',
    }

  }

};

Our PostTag model (we're creating it manually, we don't want Sails.js to create it automatically):

/**
 * PostTag.js
 *
 * @description :: A model definition.  Represents a database table/collection/etc.
 * @docs        :: https://sailsjs.com/docs/concepts/models-and-orm/models
 */

module.exports = {

  attributes: {

    post: {
      model: 'Post'
    },

    tag: {
      model: 'Tag'
    },

    customField: {
      type: 'string'
    }

  }

};

The PostTag model is actually the join table. In this model, you can define your extra fields.

Hope this helps someone using Sails v0.12.