Sequelize.JS多对多findAll不使用联接表

Sequelize.JS many-to-many findAll not using join table

本文关键字:JS findAll Sequelize      更新时间:2023-09-26

我有以下Sequelize设置:

var sequelize = new Sequelize('...', '...', '...', {
    host: '...',
    port: '3306',
    logging:  true,
    define: {
        timestamps: true,
        paranoid: true
    }
});
var Period = sequelize.define('Period', {
    name: { type: Sequelize.STRING, allowNull: false },
    periodStart: { type: Sequelize.INTEGER },
    periodEnd: { type: Sequelize.INTEGER }
});
var Material = sequelize.define('Material', {
    name: { type: Sequelize.STRING, allowNull: false }
});
var Artefact = sequelize.define('Artefact', {
    accession: { type: Sequelize.STRING, allowNull: false },
    simpleName: { type: Sequelize.STRING },
    dimensions: { type: Sequelize.STRING },
    thumbnail: { type: Sequelize.TEXT },
    storageDescriptor: { type: Sequelize.TEXT },
    description: { type: Sequelize.TEXT }
});

Artefact.hasMany(Period);
Period.hasMany(Artefact);
Artefact.hasMany(Material);
Material.hasMany(Artefact);
Period.hasMany(Period, { as: 'children', foreignKey: 'parentId', useJunctionTable: false });
Period.belongsTo(Period, { as: 'parent', foreignKey: 'parentId' });
Material.hasMany(Material, { as: 'children', foreignKey: 'parentId', useJunctionTable: false });
Material.belongsTo(Material, { as: 'parent', foreignKey: 'parentId' });
sequelize.sync().success(function () 
{
    console.log('sync OK');
    Period.findAll({ include: [ Artefact ] }).success(function (periods)
    {
        console.log(JSON.stringify(periods, null, 4));
    });
}
).error(function (err)
{
    console.log('sync FAIL');
    throw err;
});

sync()调用导致以下CREATE语句:

CREATE TABLE IF NOT EXISTS `Periods` (`name` VARCHAR(255) NOT NULL, `periodStart` INTEGER, `periodEnd` INTEGER, `id` INTEGER NOT NULL auto_increment , `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `deletedAt` DATETIME, `parentId` INTEGER, PRIMARY KEY (`id`)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `Materials` (`name` VARCHAR(255) NOT NULL, `id` INTEGER NOT NULL auto_increment , `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `deletedAt` DATETIME, `parentId` INTEGER, PRIMARY KEY (`id`)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `Artefacts` (`accession` VARCHAR(255) NOT NULL, `simpleName` VARCHAR(255), `dimensions` VARCHAR(255), `thumbnail` TEXT, `storageDescriptor` TEXT, `description` TEXT, `id` INTEGER NOT NULL auto_increment , `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `deletedAt` DATETIME, `categoryId` INTEGER, `findLocationId` INTEGER, `storeLocationId` INTEGER, PRIMARY KEY (`id`)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `ArtefactsPeriods` (`PeriodId` INTEGER , `ArtefactId` INTEGER , `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `deletedAt` DATETIME, PRIMARY KEY (`PeriodId`, `ArtefactId`)) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS `ArtefactsMaterials` (`MaterialId` INTEGER , `ArtefactId` INTEGER , `createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, `deletedAt` DATETIME, PRIMARY KEY (`MaterialId`, `ArtefactId`)) ENGINE=InnoDB;

这正是我所期望的。但是,对Period.findAll(...)的调用会导致以下SQL和异常:

Executing: SELECT `Periods`.*, `Artefacts`.`accession` AS `Artefacts.accession`, `Artefacts`.`simpleName` AS `Artefacts.simpleName`, `Artefacts`.`dimensions` AS `Artefacts.dimensions`, `Artefacts`.`thumbnail` AS `Artefacts.thumbnail`, `Artefacts`.`storageDescriptor` AS `Artefacts.storageDescriptor`, `Artefacts`.`description` AS `Artefacts.description`, `Artefacts`.`id` AS `Artefacts.id`, `Artefacts`.`createdAt` AS `Artefacts.createdAt`, `Artefacts`.`updatedAt` AS `Artefacts.updatedAt`, `Artefacts`.`deletedAt` AS `Artefacts.deletedAt`, `Artefacts`.`categoryId` AS `Artefacts.categoryId`, `Artefacts`.`findLocationId` AS `Artefacts.findLocationId`, `Artefacts`.`storeLocationId` AS `Artefacts.storeLocationId` FROM `Periods` LEFT OUTER JOIN `Artefacts` AS `Artefacts` ON `Periods`.`id` = `Artefacts`.`PeriodId`;
Error: ER_BAD_FIELD_ERROR: Unknown column 'Artefacts.PeriodId' in 'on clause'
    at Query.Sequence._packetToError (/Users/ndjm8/Work/dev/sandbox/node_modules/mysql/lib/protocol/sequences/Sequence.js:32:14)
    at Query.ErrorPacket (/Users/ndjm8/Work/dev/sandbox/node_modules/mysql/lib/protocol/sequences/Query.js:82:18)
    at Protocol._parsePacket (/Users/ndjm8/Work/dev/sandbox/node_modules/mysql/lib/protocol/Protocol.js:172:24)
    at Parser.write (/Users/ndjm8/Work/dev/sandbox/node_modules/mysql/lib/protocol/Parser.js:62:12)
    at Protocol.write (/Users/ndjm8/Work/dev/sandbox/node_modules/mysql/lib/protocol/Protocol.js:37:16)
    at Socket.ondata (stream.js:51:26)
    at Socket.EventEmitter.emit (events.js:117:20)
    at Socket.<anonymous> (_stream_readable.js:736:14)
    at Socket.EventEmitter.emit (events.js:92:17)
    at emitReadable_ (_stream_readable.js:408:10)
    --------------------
    at Query.Sequence (/Users/ndjm8/Work/dev/sandbox/node_modules/mysql/lib/protocol/sequences/Sequence.js:15:20)
    at new Query (/Users/ndjm8/Work/dev/sandbox/node_modules/mysql/lib/protocol/sequences/Query.js:12:12)
    at Function.Connection.createQuery (/Users/ndjm8/Work/dev/sandbox/node_modules/mysql/lib/Connection.js:48:10)
    at Connection.query (/Users/ndjm8/Work/dev/sandbox/node_modules/mysql/lib/Connection.js:100:26)
    at module.exports.Query.run (/Users/ndjm8/Work/dev/sandbox/node_modules/sequelize/lib/dialects/mysql/query.js:26:17)
    at module.exports.execQueueItem (/Users/ndjm8/Work/dev/sandbox/node_modules/sequelize/lib/dialects/mysql/connector-manager.js:313:21)
    at /Users/ndjm8/Work/dev/sandbox/node_modules/sequelize/lib/dialects/mysql/connector-manager.js:269:25
    at adjustCallback (/Users/ndjm8/Work/dev/sandbox/node_modules/sequelize/node_modules/generic-pool/lib/generic-pool.js:187:7)
    at dispense (/Users/ndjm8/Work/dev/sandbox/node_modules/sequelize/node_modules/generic-pool/lib/generic-pool.js:211:9)
    at Object.me.acquire (/Users/ndjm8/Work/dev/sandbox/node_modules/sequelize/node_modules/generic-pool/lib/generic-pool.js:260:5)

它似乎没有尝试使用作为sync()结果创建的ArtefactsPeriods表。我不知道自己做错了什么。PeriodMaterial的自参照关系都按预期运行,只是多对多不起作用。

无论如何,经过一些挖掘,这是一个已知的问题,在上讨论

  1. https://github.com/sequelize/sequelize/issues/515
  2. https://github.com/sequelize/sequelize/issues/745