I am a beginner to Sequelize and the concept of ORMs altogether. I have successfully converted my query to Sequelize findAll() function but it returns unwanted result. The PostgreSQL query works and returns the result perfectly fine.
Tables with Relationships:
- writers(writerId PK, writerName)
- posts(postId PK, postTitle, postDescription)
- skillmatrix(skillmatrixId PK, writerId FK, postId FK, writerSkill)
My query:
SELECT writers."writerName", posts."postTitle", posts."postDescription",
skillmatrix.*
FROM writers
INNER JOIN skillmatrix
ON writers."writerId" = skillmatrix."writerId"
INNER JOIN posts
ON skillmatrix."postId" = posts."postId"
ORDER BY writers."writerId", posts."postId"
Output from SQL Query:
Relations in Sequelize:
db.skillmatrix.hasMany(db.posts,{foreignKey: 'postId'});
db.skillmatrix.hasMany(db.writers,{foreignKey: 'writerId'});
findAll() method in Sequelize:
exports.findAll = (req, res, next) => {
SkillMatrix.findAll({
include: [
{
model: Writer,
required: true
},
{
model: Post,
required: true
}
],
order:[
['"writerId"', 'ASC'],
['"postId"', 'ASC']
]
})
.then(skillmatrix => {
res.status(200).json(skillmatrix);
})
.catch(err => {
console.log(err);
res.status(500).json({msg: "error", details: err});
});
};
JSON output from Sequelize:
{
"skillMatrixId": 1,
"writerId": 1,
"postId": 1,
"writerSkill": "None",
"writers": [
{
"writerId": 1,
"writerName": "Writer1"
}
],
"posts": [
{
"postId": 1,
"postTitle": "Post1"
"postDescription": "This is Post1"
}
]
},
{
"skillMatrixId": 2,
"writerId": 1,
"postId": 2,
"writerSkill": "SEO",
"writers": [
{
"writerId": 2,
"writerName": "Writer2" //This is unexpected
}
],
"posts": [
{
"postId": 2,
"postTitle": "Post2",
"postDescription": "This is Post2"
}
]
},
{
"skillMatrixId": 3
"writerId": 1,
"postId": 3,
"writerSkill": "Proofread"
"writers": [
{
"writerId": 3, //Unexpected
"writerName": "Writer3"
}
],
"posts": [
{
"postId": 3,
"postTitle": "Post3",
"postDescription": "This is Post3"
}
]
}...
Kindly let me know, what am I doing wrong. Also suggest me some good resources where I can learn this in deep. Thank you.
EDIT:
Sequelize log query:
SELECT "skillmatrix"."skillMatrixId",
"skillmatrix"."writerId", "skillmatrix"."postId",
"skillmatrix"."writerSkill",
"writers"."writerId" AS "writers"."writerId",
"writers"."writerName" AS "writers"."writerName",
"posts"."postId" AS "posts"."postId",
"posts"."postTitle" AS "posts"."postTitle",
"posts"."postDescription" AS "posts"."postDescription",
"posts"."writerId" AS "posts"."writerId"
FROM "skillmatrix" AS "skillmatrix"
INNER JOIN "writers" AS "writers" ON "skillmatrix"."skillMatrixId" =
"writers"."writerId"
INNER JOIN "posts" AS "posts" ON "skillmatrix"."skillMatrixId" =
"posts"."postId"
ORDER BY "skillmatrix"."writerId" ASC, "skillmatrix"."postId" ASC
2
Answers
First, you should examine the sequelize log – what query is generated by your findAll? I’d guess it’s joining on a wrong field.
Sequelize has some options for many-to-many relationships. You might try:
I’m approximating the syntax – check it out in the doc: belongsToMany
Edit – your existing association can be fixed as follows:
But, keep in mind the M:M options for future tasks 🙂
Update the Sequelize relation from :
to: