node.js - Sequelize - 外键关系 - 别名问题

Question

我有两张 table 。一个是技能，一个是评分。我的问题特定于评级:
评分模型为:

var sequelize = require('../sequelizeConfig');
var Sequelize = require('sequelize');

var Ratings=sequelize.define('ratings',{
  ratingID:{
     type:Sequelize.BIGINT,
     field:'rating_id',
     primaryKey:true
  },
   empID:{
       type: Sequelize.STRING,
       field:'emp_id'
    },
   skillID:{
       type:Sequelize.STRING,
       field:'skill_id',
       references: {
        model: 'skills',
        key: 'skill_id'
       }
    },
   rating:{
    type: Sequelize.BIGINT ,
  field:'rating'  
}
},{
    tableName:'ratings',
    freezeTableName: true 
  })

module.exports=Ratings;

在 Controller 中，我有以下关系。我不确定使用 hasOne 是否正确。只有“belongsTo”来说明外键关系，我只得到了与技能无关的评级。基本上评分表中的skill_id 引用了技能表中的skill_id。

var skillsets = require('../../models/skillsets');
var ratings = require('../../models/ratings');
skillsets.hasOne(ratings,{foreignKey:'skillID',sourceKey:'skillsetID'});
ratings.belongsTo(skillsets,{foreignKey:'skillID',targetKey:'skillsetID'});

我尝试查询如下:

skillsets.findAll({
            where:{
                roleID: role
            },
            include:[{
                model:ratings,
                where:{
                    emp_id: empID
                },
                attributes:['rating'],
                required:false
            }]
        })

一切看起来都很好，但这 hasOne 做了一些可疑的事情。
它生成查询为:

SELECT "skills"."skill_id" AS "skillsetID", "skills"."role_id" AS "roleID", "skills"."field", "skills"."skill_name" AS "skillName", "rating"."rating_id" AS "rating.ratingID", "rating"."rating" AS "rating.rating" FROM "skills" AS "skills" LEFT OUTER JOIN "ratings" AS "rating" ON "skills"."skill_id" = "rating"."skill_id" AND "rating"."emp_id" = '12344' WHERE "skills"."role_id" = 'developer';

由于 "rating"."rating"AS "rating.rating"，结果如下:

[
    {
        "skillsetID": "Angular",
        "roleID": "developer",
        "field": "Frontend",
        "skillName": "Angular",
        "rating": 
            {
                "rating": "4"
            }

}]

我期望的是:

  [
        {
            "skillsetID": "Angular",
            "roleID": "developer",
            "field": "Frontend",
            "skillName": "Angular",
            "rating":4
    }]

可以请你帮忙吗？

Answer 1

这种包含模型 ratings 的嵌套是 sequelize 中的默认行为。您可以传入选项 raw: true 以获得如下所示的结果:

{
    "skillsetID": "Angular",
    "roleID": "developer",
    "field": "Frontend",
    "skillName": "Angular",
    "rating.rating": "4",
}

如果您不想在键中使用点符号，那么您可以像这样更改您的 Sequelize 查询:

var sequelize = require('../sequelizeConfig'); 

skillsets.findAll({
    attributes: {
        include: [ // Include these attributes along with all other attributes
            [
                sequelize.literal('rating.rating'), // get `rating` from `ratings` table in the query
                'rating', // use `rating` as alias
            ]
        ]
    },
    where: {
        roleID: role
    },
    include: [
        {
            model: ratings,
            where: {
                emp_id: empID
            },
            attributes: [], // This tells sequelize to not nest any attributes inside ratings object
            required: false
        }
    ]
});

这将为您提供所需的结果:

{
    "skillsetID": "Angular",
    "roleID": "developer",
    "field": "Frontend",
    "skillName": "Angular",
    "rating": "4",
}

hasOne 和belongsTo:
sequelize 中的关系的目的是它们允许您在查询时对相关模型进行 include 并在创建表时建立关系。如果您不添加 hasOne 关系。然后sequelize会告诉你 skills不属于 rating，即使 skillID表里面有外键 ratings。 hasOne 告诉 sequelize 可以将模型 ratings 包含在 skills 中，并具有 1-1 关系