CODEWITHSUNDEEP
javascriptsqlnode.jssequelize.js
yoni349
yoni349

Reputation: 117

Sequelize associations (join) erorr: Error: Unknown column

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?

Upvotes: 0

Views: 110

Answers (1)

Anatoly
Anatoly

Reputation: 22758

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.

Upvotes: 2

Related Questions