Sequelizejs belongsToMany 与 otherKey 的关系

2022-01-19 00:00:00 node.js javascript sequelize.js

我正在创建一个关于歌曲和艺术家的应用程序，以下是数据库架构:

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

Song 有很多 Artists，Artist 有很多 Songs，这是多对多的关系，所以我定义了一个连接表 SongArtist:

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

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

默认行为是 SongArtist 使用 Songs 和 Artists 主键('id') 来查询，但我想使用 歌曲和艺术家中的 neteaseId 列对多对多查询，所以我在下面使用 otherKey:

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:

歌曲模型:

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

艺人模特:

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

那么如何在多对多查询中更改默认查询列，并应生成以下sql:

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"

而不是

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

推荐答案

您使用的otherKey 选项不是用于选择关联中使用的源模型的字段.根据otherKey

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

连接表中外键的名称(表示目标模型)或表示另一列类型定义的对象(语法见 Sequelize.define).使用对象时，可以添加 name 属性来设置列的名称.默认为目标名称+目标主键

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

它为目标模型指定连接表中的外键，因此它是连接表中的列，而不是源/目标表中的列.更何况，根据Sequelize的源码belongsToMany，在这种关联中不能使用除主键以外的其他字段.下面是从源模型中选择字段的一行代码

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

this.source 是你的源模型，所以是 Song 或 Artist.如您所见，它会自动选择此模型的 primaryKeyAttribute，在这两种情况下都是 id 字段.github上甚至存在一个关于您遇到的相同问题的问题，答案说在这种情况下只有主键字段是可以接受的 - 在belongsToMany关联中指定非主目标键

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

相关文章