Sequelizejs与otherKey的belongsToMany关系

问题描述

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

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:

歌曲艺术家模型:

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 属性来设置列的名称.默认为目标名称+目标主键

它为目标模型指定连接表中的外键，因此它是连接表中的列，而不是源/目标表中的列.更何况，根据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

这篇关于Sequelizejs与otherKey的belongsToMany关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！