Node.js Sequelize中关联表如何获取外键关联的用户全字段

我有一个基于Node.js、Express框架，使用Sequelize作为ORM、PostgreSQL作为数据库的应用。应用中包含candidat（候选人）和mission（任务）模型，二者通过candidat_mission中间表建立多对多关联。该中间表还包含指向user（用户）表的外键fk_user。当前发起GET请求时，candidat_mission仅返回用户ID，我希望返回用户模型的全部字段。

相关模型及控制器代码如下：

candidat模型 #

'use strict';
const {
    Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
    class candidat extends Model {

        static associate(models) {
            this.belongsToMany(models.mission, {
                through: "candidat_mission",
                foreignKey: "candidatId",
                otherKey: "idMission",
            });
        }
    }
    candidat.init({
        candidatId: {
            allowNull: false,
            autoIncrement: true,
            primaryKey: true,
            type: DataTypes.INTEGER
        },
        lastName: {
            type: DataTypes.STRING,
            allowNull: false,
        },
        firstName: {
            type: DataTypes.STRING,
            allowNull: false,
        },
        email: {
            isEmail: true,
            allowNull: false,
            type: DataTypes.STRING,
            unique: true,
        },

    }, {
        sequelize,
        modelName: 'candidat',
        tableName: 'candidat',
        freezeTableName: true,
    });
    return candidat;
};

mission模型 #

'use strict';
const {
    Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
    class mission extends Model {

        static associate(models) {
            this.belongsToMany(models.candidat, {
                through: "candidat_mission",
                foreignKey: "idMission",
                otherKey: "candidatId",
            })
        }
    }
    mission.init({
        idMission: {
            allowNull: false,
            autoIncrement: true,
            primaryKey: true,
            type: DataTypes.INTEGER
        },
        title: {
            type: DataTypes.STRING,
            allowNull: false
        },
        aliasTitle: {
            type: DataTypes.STRING,
            allowNull: true
        },
        description: {
            type: DataTypes.TEXT,
            allowNull: true
        }
    }, {
        sequelize,
        modelName: 'mission',
        tableName: 'mission',
        freezeTableName: true,
    });
    return mission;
};

candidat_mission中间表模型 #

'use strict';
const {
    Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
    class candidat_mission extends Model {

        static associate(models) {
            this.belongsTo(models.user, { foreignKey: "fk_user" });
        }
    }
    candidat_mission.init({
        candidatMissionId: {
            allowNull: false,
            autoIncrement: true,
            primaryKey: true,
            type: DataTypes.INTEGER
        },
        candidatId: {
            type: DataTypes.INTEGER,
            allowNull: false,
            references: {
                model: { tableName: 'candidat' },
                key: "candidatId",
            },
        },
        idMission: {
            type: DataTypes.INTEGER,
            allowNull: false,
            references: {
                model: { tableName: 'mission' },
                key: "idMission",
            },
        },
        fk_user: {
            type: DataTypes.INTEGER,
            allowNull: true,
            references: {
                model: { tableName: 'user' },
                key: "userId",
            },
        },
    }, {
        sequelize,
        modelName: 'candidat_mission',
        tableName: 'candidat_mission',
        timestamps: true,
        freezeTableName: true,
    });
    return candidat_mission;
};

任务控制器添加候选人函数 #

const addCandidats = async (req, res) => {
    try {
        const mission = await Mission.findByPk(req.body.idMission);

        if (mission) {
            const candidat = await Candidat.findByPk(req.body.candidatId);
            if (candidat) {
                mission.addCandidat(candidat, 
                    {through: {
                        fk_user: req.body.fk_user && req.body.fk_user
                    }});
                return res.status(200).send(mission);
            } else {
                console.log("Candidat non trouvé");
                return null;
            }
        } else {
            console.log("Mission non trouvée!");
            return null;
        }

    } catch (error) {
        console.log(error);
    }
};

当前返回结果：

{
  "candidatMissionId": 2,
  "candidatId": 1,
  "idMission": 7,
  "fk_user": 1,
  "createdAt": "2023-02-14T10:34:08.302Z",
  "updatedAt": "2023-02-14T15:06:10.232Z"
}

期望返回结果：

{
 "candidatMissionId": 2,
                "candidatId": 1,
                "idMission": 7,
                "fk_user": {
            "userId": 1,
            "email": "blabla@gmail.com",
            "name": "blabla"
        },
                "createdAt": "2023-02-14T10:34:08.302Z",
                "updatedAt": "2023-02-14T15:06:10.232Z"
}

要让中间表返回完整的用户信息，需要在多对多关联配置中指定加载用户关联，并在查询时显式触发该关联加载：

1. 更新模型关联配置 #

修改candidat模型的associate方法 #

在belongsToMany的through选项中，添加要包含的用户模型：

static associate(models) {
  this.belongsToMany(models.mission, {
    through: {
      model: "candidat_mission",
      include: [{ model: models.user, as: 'user' }]
    },
    foreignKey: "candidatId",
    otherKey: "idMission",
  });
}

修改mission模型的associate方法 #

同样在belongsToMany的through选项中添加用户模型：

static associate(models) {
  this.belongsToMany(models.candidat, {
    through: {
      model: "candidat_mission",
      include: [{ model: models.user, as: 'user' }]
    },
    foreignKey: "idMission",
    otherKey: "candidatId",
  })
}

优化candidat_mission模型的关联 #

给用户关联添加别名，确保和上面配置的一致：

static associate(models) {
  this.belongsTo(models.user, { foreignKey: "fk_user", as: 'user' });
}

2. 修改查询逻辑，加载关联数据 #

在addCandidats函数中，添加候选人后需要重新查询并加载完整的关联数据：

const addCandidats = async (req, res) => {
  try {
    const mission = await Mission.findByPk(req.body.idMission);
    if (mission) {
      const candidat = await Candidat.findByPk(req.body.candidatId);
      if (candidat) {
        await mission.addCandidat(candidat, {
          through: { fk_user: req.body.fk_user }
        });
        // 重新查询并加载包含用户信息的关联数据
        const updatedMission = await Mission.findByPk(req.body.idMission, {
          include: [{
            model: Candidat,
            through: {
              include: [{ model: User, as: 'user' }]
            }
          }]
        });
        return res.status(200).send(updatedMission);
      } else {
        console.log("候选人未找到");
        return res.status(404).json({ message: '候选人未找到' });
      }
    } else {
      console.log("任务未找到");
      return res.status(404).json({ message: '任务未找到' });
    }
  } catch (error) {
    console.log(error);
    res.status(500).json({ error: '操作失败' });
  }
};

3. 调整返回数据结构（可选） #

如果希望把用户数据直接放在fk_user字段下，可以在返回前手动处理：

// 处理updatedMission中的中间表数据
updatedMission.candidats.forEach(candidat => {
  candidat.candidat_mission.fk_user = candidat.candidat_mission.user;
  delete candidat.candidat_mission.user;
});
return res.status(200).send(updatedMission);

内容的提问来源于stack exchange，提问作者PierroTH