CODEWITHSUNDEEP
javascriptnode.jssequelize.js
Moon soon
Moon soon

Reputation: 2856

Sequelizejs belongsToMany relation with otherKey

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"

Upvotes: 5

Views: 5596

Answers (2)

AndyFaizan
AndyFaizan

Reputation: 1893

Support for targetKey and sourceKey for many-to-many relations was added in Sequelize 5.15.0 as pointed out in this PR.

Quoting the docs here.

There are four cases to consider:

We might want a many-to-many relationship using the default primary keys for both Foo and Bar:

Foo.belongsToMany(Bar, { through: 'foo_bar' }); // This creates a junction table `foo_bar` with fields `fooId` and `barId`

We might want a many-to-many relationship using the default primary key > for Foo but a different field for Bar:

Foo.belongsToMany(Bar, { through: 'foo_bar', targetKey: 'title' }); // This creates a junction table `foo_bar` with fields `fooId` and `barTitle`

We might want a many-to-many relationship using the a different field for Foo and the default primary key for Bar:

Foo.belongsToMany(Bar, { through: 'foo_bar', sourceKey: 'name' }); // This creates a junction table `foo_bar` with fields `fooName` and `barId`

We might want a many-to-many relationship using different fields for both Foo and Bar:

Foo.belongsToMany(Bar, { through: 'foo_bar', sourceKey: 'name', targetKey: 'title' }); // This creates a junction table `foo_bar` with fields `fooName` and `barTitle`

Code example shown in this commit. Pasting here for quick reference.

const User = this.sequelize.define('User', {
  id: {
    type: DataTypes.UUID,
    allowNull: false,
    primaryKey: true,
    defaultValue: DataTypes.UUIDV4,
    field: 'user_id'
  },
  userSecondId: {
    type: DataTypes.UUID,
    allowNull: false,
    defaultValue: DataTypes.UUIDV4,
    field: 'user_second_id'
  }
}, {
  tableName: 'tbl_user',
  indexes: [
    {
      unique: true,
      fields: ['user_second_id']
    }
  ]
});
const Group = this.sequelize.define('Group', {
  id: {
    type: DataTypes.UUID,
    allowNull: false,
    primaryKey: true,
    defaultValue: DataTypes.UUIDV4,
    field: 'group_id'
  },
  groupSecondId: {
    type: DataTypes.UUID,
    allowNull: false,
    defaultValue: DataTypes.UUIDV4,
    field: 'group_second_id'
  }
}, {
  tableName: 'tbl_group',
  indexes: [
    {
      unique: true,
      fields: ['group_second_id']
    }
  ]
});
User.belongsToMany(Group, {
  through: 'usergroups',
  sourceKey: 'userSecondId'
});
Group.belongsToMany(User, {
  through: 'usergroups',
  sourceKey: 'groupSecondId'
});

Upvotes: 4

piotrbienias
piotrbienias

Reputation: 7401

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

Upvotes: 7

Related Questions