Sequelize关联(连接)错误:错误:未知列

huangapple go评论62阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年1月6日 01:18:51
  • 转载请务必保留本文链接:https://go.coder-hub.com/75022125.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定