将SQL查询转换为后缀ORM返回不需要的结果。

Question

我是一个新的续集和奥姆斯的概念的初学者。我已经成功地将我的查询转换为对findAll()函数进行后缀，但它返回不需要的结果。PostgreSQL查询工作正常，并很好地返回结果。

有关系的表格：

1. 作者(writerId PK，writerName)
2. 职位(postId PK，postTitle，postDescription)
3. 技能矩阵(skillmatrixId PK、writerId FK、postId FK、writerSkill)

我的问题是：

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"

SQL查询的输出：

​

​

后续关系：

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

后缀中的findAll()方法：

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的后缀输出：

{
    "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"
        }
    ]
}...

请告诉我，我做错了什么。还建议我一些好的资源，在那里我可以深入了解这一点。谢谢。

编辑：

Sequelize日志查询：

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

Answer 1

从以下位置更新后缀关系：

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

至：

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

Answer 2

首先，您应该检查后缀日志--您的findAll生成了什么查询？我猜它加入了一个错误的领域。

“续集”对于多到多的关系有一些选择。你可以试试：

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
   }
});

我正在近似于语法-在doc：belongsToMany中查看它

编辑-您现有的关联可以按以下方式修正：

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

但是，请记住未来任务的M:M选项:)