英文:
Sequelize associations (join) erorr: Error: Unknown column
问题
Sequelize为什么在选择语句中使用了wp_postmeta.ID？在join部分使用wp_postmeta.post_id是正确的。为什么在之后混合两个表的字段呢？
(Note: I've only translated the requested portion of the text.)
英文:
I have 2 simple tables:
table: wp_posts
PK ID
post_title
table: wp_postmeta
PK post_id
meta_key
meta_value
Running this code:
wp_posts.hasMany(wp_postmeta , { foreignKey: "post_id" });
wp_postmeta.belongsTo(wp_posts , { foreignKey: "ID" });
wpPostsData = await wp_posts .findAll({
include: [wp_postmeta ]
});
Generates the following (i can see that in the terminal):
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';
Ending in this error:
parent: Error: Unknown column 'wp_postmeta.ID' in 'fie… at Packet.asError (c:\Users\yonatan\OneD…, original: Error: Unknown column 'wp_postmeta.ID' in 'fi… at Packet.asError (c:\Users\yonatan\OneD…, sql: 'SELECT `wp_posts`.`ID`, `wp_posts`.`post_autho…_id` WHERE `wp_posts`.`post_type` = 'product';', parameters: {…}, …}
Why is Sequelize using wp_postmeta.ID in the select statements?
It's getting it right in the join part where it uses wp_postmeta.post_id.
So why mix the tables fields after it?
答案1
得分: 2
你需要在成对的关联（hasMany/belongsTo）中指定相同的外键列，因为实际上你只有一个外键列（在 wp_postmeta 中），而 wp_posts 中的另一列是主键（默认情况下）。
wp_posts.hasMany(wp_postmeta, { foreignKey: "post_id" });
wp_postmeta.belongsTo(wp_posts, { foreignKey: "post_id" });
在关联中，通常不需要指定1:N关系中一侧表格的列，因为通常它是主键列。如果你想要使用具有唯一约束/索引的主键以外的其他列，那么你也需要在sourceKey选项中指定它。
英文:
You need to indicate the same foreign key column in both paired associations (hasMany/belongsTo) because actually you have only one foreign key column (in wp_postmeta) and the other column in wp_posts is PK (by default).
wp_posts.hasMany(wp_postmeta , { foreignKey: "post_id" });
wp_postmeta.belongsTo(wp_posts , { foreignKey: "post_id" });
There is no need to indicate the column from 1 side table of 1:N relationship in an association because usually it's the PK column. If you want to use other column instead of PK that has a unique constraint/index then you need to indicate it as well in both associations in sourceKey option.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论