Sequelizejs belongsToMany relation with otherKey

Question

I am creating an application about songs and artists, following is the database schema:

Song has many Artists, and Artist has many Songs, this is a many to many relations, so I define a join table SongArtist:

SongArtist Model:

module.exports = function(sequelize, DataTypes) {
  var SongArtist = sequelize.define('SongArtist', {
    id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    songId: {
      type: DataTypes.INTEGER,
      allowNull: false,

    },
    artistId: {
      type: DataTypes.INTEGER,
      allowNull: false,

    }
  }, {
    tableName: 'SongArtist',
  });
  return SongArtist;
};

The default behavior is SongArtist use Songs and Artists primary key('id') to query, but I wanna use neteaseId column in Songs and Artists to many to many query, so I am using otherKey in following:

Songs Model:

module.exports = function(sequelize, DataTypes) {
  var Songs = sequelize.define('Songs', {
    id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    name: {
      type: DataTypes.STRING,
      allowNull: true
    },
    neteaseId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      unque: true
    }
  }, {
    tableName: 'Songs',
    classMethods: {
      associate: (models) => {
        Songs.belongsToMany(models.Artists,{
          through: 'SongArtist',
          foreignKey: 'songId',
          otherKey: 'neteaseId'
        })
      }
    }
  });

  return Songs;
};

Artists Model:

module.exports = function(sequelize, DataTypes) {
  var Artists = sequelize.define('Artists', {
    id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    name: {
      type: DataTypes.STRING,
      allowNull: true
    },
    neteaseId: {
      type: DataTypes.INTEGER,
      allowNull: false,
      unque: true
    }
  }, {
    tableName: 'Artists',
    classMethods: {
      associate: (models) => {
        Artists.belongsToMany(models.Songs,{
          through: 'SongArtist',
          foreignKey: 'artistId',
          otherKey: 'neteaseId'
        })
      }
    }
  });

  return Artists;
};

But when I execute query with following code it throws error to me:

models.Songs.findAll({include: [models.Artists, models.Album]})

> SequelizeDatabaseError: column Artists.SongArtist.neteaseId does not exist

So how to change default query column in many to many query, and should generate following sql:

LEFT OUTER JOIN ("SongArtist" AS "Artists.SongArtist"
INNER JOIN "Artists" AS "Artists" ON "Artists"."neteaseId" =    "Artists.SongArtist"."artistId") 
ON "Songs"."id" = "Artists.songId"."songId"

instead of

LEFT OUTER JOIN ("SongArtist" AS "Artists.SongArtist"
INNER JOIN "Artists" AS "Artists" ON "Artists"."id" =   "Artists.SongArtist"."artistId") 
ON "Songs"."id" = "Artists.SongArtist"."songId"

Answer 1

The otherKey option you are using is not for selecting the field of source model used in the association. According to the documentation of otherKey

The name of the foreign key in the join table (representing the target model) or an object representing the type definition for the other column (see Sequelize.define for syntax). When using an object, you can add a name property to set the name of the column. Defaults to the name of target + primary key of target

It specifies foreign key in join table for target model, so it is a column in the join table, not in source/target table. What is more, according to the source code of Sequelize belongsToMany, it is not possible to use other field than primary key in this kind of association. Below is a line of code that selects the field from source model

const sourceKey = this.source.rawAttributes[this.source.primaryKeyAttribute];

Where this.source is your source model so rather Song or Artist. As you can see, it automatically picks the primaryKeyAttribute of this model, which in both cases is id field. There exists even an issue on the github concerning the same problem you experienced, and the answer says that only the primary key field is acceptable in this case - Specify non-primary target key in belongsToMany association