seo Question posted in
Some basic information to get you started with this can be found here.

Converting SQL query to Sequelize ORM returns unwanted result – SEO

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
Tags:

2

Answers


  1. 0

    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 🙂

    Login or Signup to reply.
  2. 0

    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'});
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search