CODEWITHSUNDEEP
node.jsexpressormsequelize.jshas-and-belongs-to-many
iwaduarte
iwaduarte

Reputation: 1700

Sequelize BelongsToMany self reference inverse SQL QUERY

Hello everyone hope you day is being great. I have searched all through the internet and here is my last line of hope. Hopefully some beautiful soul will explain to me why is that happening because I could not grasp from the documentations or other Q&A here on stack overflow the light on this situation.

The case is kind of simple: In short I am getting the inverse SQL query.

I have this self reference association:

User.belongsToMany(User, {as: 'parents', through: 'kids_parents',foreignKey: 'parent', otherKey: 'kid'}); 
User.belongsToMany(User, {as: 'kids', through: 'kids_parents', foreignKey: 'kid',otherKey: 'parent'});

then in my controller I have this:

User.findById(2).then((parent) => {
      parent.getKids().then((kids)=> {
          console.log(kids);
      });

I would be expecting to get ALL kids from the parent instance. Is that right ? Instead I am getting the opposite ALL parents from the specific KID id.

 SELECT `user`.`id`, `user`.`name`, `user`.`surname`, `user`.`username`,  `kids_parents`.`id` AS `kids_parents.id`, `kids_parents`.`kid` AS `kids_parents.kid`, `kids_parents`.`parent` AS `kids_parents.parent` FROM `users` AS `user` INNER JOIN `kids_parents` AS `kids_parents` ON **`user`.`id` = `kids_parents`.`parent`** AND **`kids_parents`.`kid` = 2;**

and note this line:

user.id = kids_parents.parent AND kids_parents.kid = 2;

Can someone explain why is that happening ? What am I missing here ? Thanks for the attention.

Upvotes: 3

Views: 563

Answers (1)

iwaduarte
iwaduarte

Reputation: 1700

I figure it out after taking a rest and re-analysing the documentation.Find out that the foreignKey: atribute on the association relates to the source instance and NOT to the target BUT (and that was the confusing part for me) the as: atribute relates to the target instance and not to the source.

User(Kid).belongsToMany(User(Parents), {as:(target) 'parents', through: 'kids_parents',foreignKey(source): 'parent' (wrong!!!), otherKey: 'kid'});

so instead of the line above, should be:

User.belongsToMany(User, {as: 'parents', through: 'kids_parents',foreignKey: 'kid' , otherKey: 'parent'});

and:

User.belongsToMany(User, {as: 'kids', through: 'kids_parents', foreignKey: 'parent',otherKey: 'kid'});

Now parent.getKids() works as expected!

Upvotes: 3

Related Questions