无法为Sequelize查询结果对象动态添加属性问题排查

我尝试查询数据库获取用户的firstname和lastname，通过对象映射动态添加到返回前端的结果中，但最终返回值里没有这些字段。以下是相关代码、实际返回和预期返回：

代码实现 #

const getMyCrushRequest = async (req, res) => {
    // Check if user has sent any crush requests
    const findCrush = await CrushRequest.findAll({ 
        attributes: {exclude: ['requestFrom']}, 
        where: {requestFrom: req.user.id}
    });

    // If user has not sent any crush requests
    if(!findCrush || findCrush.length == 0){
        return res.status(StatusCodes.OK).send({
            success: false, 
            message: 'you have not sent any crush requests'
        });
    }

    
    // Map the crush requests to include the first and last name of the recipient
    const requests = await Promise.all(findCrush.map(async (crush) => {
        const nameQuery = await User.findOne({ 
            attributes: ["firstname", "lastname"], 
            where: {id: crush.requestTo}
        });

        // Add the user details to the crush request object
        crush.firstname = nameQuery.firstname;
        crush.lastname = nameQuery.lastname;

        return crush;
    }));
     
    // console.log (JSON.stringify(requests))
    // Return the mapped crush requests
    return res.status(StatusCodes.OK).json({
        success: true, 
        message: 'Crush request retrieved successfully', 
        requests: requests
    });
}

实际返回结果 #

{
    "success": true,
    "message": "Crush request retrieved successfully",
    "requests": [
        {
            "id": 2,
            "requestTo": "d8b60bcb-c1f4-41c9-8f0b-4f3baf734b06",
            "requestStatus": "pending",
            "createdAt": "2022-12-12T16:45:02.000Z",
            "updatedAt": "2022-12-12T16:45:02.000Z"
        },
        {
            "id": 3,
            "requestTo": "f6516bd8-2577-468c-9779-3f271b6a03db",
            "requestStatus": "pending",
            "createdAt": "2022-12-12T17:06:12.000Z",
            "updatedAt": "2022-12-12T17:06:12.000Z"
        }
    ]
}

预期返回结果 #

{
    "success": true,
    "message": "Crush request retrieved successfully",
    "requests": [
        {
            "id": 2,
            "requestTo": "d8b60bcb-c1f4-41c9-8f0b-4f3baf734b06",
            "firstname": "Somename", 
            "lastname": "someorthername",
            "requestStatus": "pending",
            "createdAt": "2022-12-12T16:45:02.000Z",
            "updatedAt": "2022-12-12T16:45:02.000Z"
        },
        {
            "id": 3,
            "requestTo": "f6516bd8-2577-468c-9779-3f271b6a03db", 
            "firstname": "Somename", 
            "lastname": "someorthername",
            "requestStatus": "pending",
            "createdAt": "2022-12-12T17:06:12.000Z",
            "updatedAt": "2022-12-12T17:06:12.000Z"
        }
    ]
}

问题原因 #

Sequelize查询返回的是模型实例对象，而非普通JavaScript对象。这类实例有自己的属性序列化规则，直接给实例添加的自定义属性（比如crush.firstname）不会被包含在默认的JSON输出里。

解决方案 #

方案1：转为普通对象后添加属性 #

在处理每个crush实例时，先将其转为普通JS对象，再添加自定义字段。可以用Sequelize实例的get({ plain: true })方法，或者ES6扩展运算符：

const requests = await Promise.all(findCrush.map(async (crush) => {
    const nameQuery = await User.findOne({ 
        attributes: ["firstname", "lastname"], 
        where: {id: crush.requestTo}
    });
    // 转为普通对象
    const crushObj = crush.get({ plain: true });
    // 添加属性
    crushObj.firstname = nameQuery.firstname;
    crushObj.lastname = nameQuery.lastname;
    return crushObj;
}));

方案2：使用关联查询（更高效，避免N+1查询） #

上面的写法会触发N+1查询问题（先查N条CrushRequest，再循环查N次User表），性能较差。更优的方式是利用Sequelize的关联查询，一次SQL获取所有数据：

第一步：定义模型关联

在CrushRequest模型文件中添加关联：

CrushRequest.belongsTo(User, { 
  foreignKey: 'requestTo', 
  as: 'recipient' // 定义别名，方便后续查询
});

第二步：修改查询代码

直接在findAll时关联查询User表，指定需要的属性：

const getMyCrushRequest = async (req, res) => {
    // 一次查询获取所有数据，包括收件人姓名
    const findCrush = await CrushRequest.findAll({ 
        attributes: {exclude: ['requestFrom']}, 
        where: {requestFrom: req.user.id},
        include: [{
            model: User,
            as: 'recipient', // 和关联定义的别名一致
            attributes: ['firstname', 'lastname'] // 仅获取需要的字段
        }]
    });

    if(!findCrush || findCrush.length == 0){
        return res.status(StatusCodes.OK).send({
            success: false, 
            message: 'you have not sent any crush requests'
        });
    }

    // 调整数据结构，将关联字段合并到主对象中
    const requests = findCrush.map(crush => {
        const crushObj = crush.get({ plain: true });
        // 合并recipient的字段
        crushObj.firstname = crushObj.recipient.firstname;
        crushObj.lastname = crushObj.recipient.lastname;
        delete crushObj.recipient; // 删除多余的关联属性
        return crushObj;
    });

    return res.status(StatusCodes.OK).json({
        success: true, 
        message: 'Crush request retrieved successfully', 
        requests: requests
    });
}

这种方式既解决了字段丢失的问题，又优化了查询性能，避免了多次数据库请求。

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