Converting SQL query to Sequelize ORM returns unwanted result

1
votes

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:

  1. writers(writerId PK, writerName)
  2. posts(postId PK, postTitle, postDescription)
  3. 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:

enter image description here

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
sqlnode.jspostgresqljoinsequelize.js
The order by is done after the entire from, so your indentation is not reflecting the meaning. PS Please use text, not images/links, for text--including tables & ERDs. Paraphrase or quote from other text. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. Make your post self-contained. - philipxy
Noted. Thanks. I'll keep that in mind. - Rachit

2 Answers

1
votes

Update the Sequelize relation from :

db.skillmatrix.hasMany(db.posts,{foreignKey: 'postId'});
db.skillmatrix.hasMany(db.writers,{foreignKey: 'writerId'});

to:

db.skillmatrix.hasMany(db.posts,{sourceKey: 'postId' , foreignKey: 'postId'});
db.skillmatrix.hasMany(db.writers,{sourceKey: 'writerId' ,foreignKey: 'writerId'});
1
votes

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: 

db.posts.belongsToMany  (db.writers, {as : 'p2w', through: db.skillMatrix, foreignKey: 'postId'   });
db.writers.belongsToMany(db.posts,   {as : 'w2p', through: db.skillMatrix, foreignKey: 'writerId' });

db.posts.findAll({
  include: { 
     model: db.writers, 
     as: 'p2w',
     required: true
   }
});

I'm approximating the syntax - check it out in the doc: belongsToMany

Edit - your existing association can be fixed as follows:

db.skillmatrix.hasMany(db.writers,{sourceKey: 'writerId', foreignKey: 'writerId'});

But, keep in mind the M:M options for future tasks :)