Sequelize associations (join) erorr: Error: Unknown column

Question

我有两个简单的表:

table: wp_posts 
PK ID
post_title
table: wp_postmeta 
PK post_id
meta_key
meta_value

运行此代码:

wp_posts.hasMany(wp_postmeta , { foreignKey: "post_id" });
wp_postmeta.belongsTo(wp_posts , { foreignKey: "ID" });
wpPostsData = await wp_posts .findAll({
include: [wp_postmeta ]
});

生成以下内容(在终端中可以看到):

Executing (default): SELECT `wp_posts`.`ID`, `wp_posts`.`post_author`, `wp_posts`.`post_title`, `wp_postmeta`.`post_id` AS `wp_postmeta.post_id`, `wp_postmeta`.`meta_key` AS `wp_postmeta.meta_key`, `wp_postmeta`.`meta_value` AS `wp_postmeta.meta_value`, `wp_postmeta`.`ID` AS `wp_postmeta.ID` FROM `wp_posts` AS `wp_posts` LEFT OUTER JOIN `wp_postmeta` AS `wp_postmeta` ON `wp_posts`.`ID` = `wp_postmeta`.`post_id` WHERE `wp_posts`.`post_type` = 'product';

以此错误结尾:

parent: Error: Unknown column 'wp_postmeta.ID' in 'fie…    at Packet.asError (c:UsersyonatanOneD…, original: Error: Unknown column 'wp_postmeta.ID' in 'fi…   at Packet.asError (c:UsersyonatanOneD…, sql: 'SELECT `wp_posts`.`ID`, `wp_posts`.`post_autho…_id` WHERE `wp_posts`.`post_type` = 'product';', parameters: {…}, …}

为什么Sequelize在select语句中使用wp_postmeta.ID?在用wp_postmeta.post_id的join部分得到了正确的结果。那么为什么要在它之后混合表字段呢?

Answer 1

您需要在两个配对关联(hasMany/belongsTo)中指定相同的外键列，因为实际上您只有一个外键列(在wp_postmeta中)，而wp_posts中的另一列是PK(默认情况下)。

wp_posts.hasMany(wp_postmeta , { foreignKey: "post_id" });
wp_postmeta.belongsTo(wp_posts , { foreignKey: "post_id" });

不需要在关联中指明1:N关系的1个边表的列，因为通常是PK列。如果您想使用其他列而不是具有唯一约束/索引的PK，那么您需要在sourceKey选项中同时在两个关联中指出它。