使用Sequelize include后brnc_name等数据库字段未显示问题

JSON返回结果里brnc_name、brn1_name、brn2_name这三个字段没显示，但直接在MySQL里执行查询时这些字段能正常出来，不知道Sequelize代码哪里出了问题。

代码如下：

async function select_all(params){
    const {brncBrncId, brn1Brn1Id, brn2_id, blnc_status, condition,limit,offset, ousr_id}=params;
   
    const balance = await db.Balance.findAndCountAll({
        include: [{
            attributes:{
                    include:[
                            sequelize.literal(`(
                                SELECT brnc.brnc_name
                                FROM brncs AS brnc
                                WHERE
                                brnc.brnc_id = ocst.brncBrncId
                                ) AS 'brnc_name'`)
                            ,
                            
                            sequelize.literal(`(
                                SELECT brn1.brn1_name
                                FROM brn1s AS brn1
                                WHERE
                                brn1.brn1_id = ocst.brn1Brn1Id
                                LIMIT 1
                                ) AS brn1_name`)
                            ,
                            sequelize.literal(`(
                                SELECT brn2.brn2_name
                                FROM brn2s AS brn2
                                WHERE
                                brn2.brn2_id = ocst.brn2_id
                                LIMIT 1
                                ) AS brn2_name`)
                            ,
                            sequelize.literal(` 
                                CASE blnc_condition 
                                WHEN '1' THEN 'NORMAL' 
                                WHEN '2' THEN 'BERMASALAH' 
                                ELSE 'MACET' 
                                END AS blnc_condition`)
                        ]
            },
            model: db.Customer,
            required: true,
        }],
        where: [brncBrncId ? {brncBrncId : brncBrncId}: {}, brn1Brn1Id ? {brn1Brn1Id : brn1Brn1Id}: {}, brn2_id ? {brn2_id : brn2_id}: {}, {blnc_status : blnc_status} , condition ? condition : {} ],
        limit: limit, 
        offset: offset
    });
    return balance;
}

核心原因 #

Sequelize默认只会返回关联模型（这里是Customer）中预先定义的属性，你通过sequelize.literal添加的自定义字段不在Customer模型的属性列表里，所以会被自动过滤掉。另外别名的单引号也可能导致字段名识别异常。

具体修复方案 #

1. 显式声明所有要返回的字段
   如果你需要保留Customer的原有属性，就在attributes里先列出这些属性，再添加自定义字段；如果不需要原有属性，直接用数组包含所有自定义字段：

   // 保留原有属性的写法（假设Customer有id、name等属性）
   attributes: {
     include: [
       'id', 'name', // 先列原有属性
       sequelize.literal(`(SELECT brnc.brnc_name FROM brncs AS brnc WHERE brnc.brnc_id = ocst.brncBrncId) AS brnc_name`),
       sequelize.literal(`(SELECT brn1.brn1_name FROM brn1s AS brn1 WHERE brn1.brn1_id = ocst.brn1Brn1Id LIMIT 1) AS brn1_name`),
       sequelize.literal(`(SELECT brn2.brn2_name FROM brn2s AS brn2 WHERE brn2.brn2_id = ocst.brn2_id LIMIT 1) AS brn2_name`),
       sequelize.literal(`CASE blnc_condition WHEN '1' THEN 'NORMAL' WHEN '2' THEN 'BERMASALAH' ELSE 'MACET' END AS blnc_condition`)
     ]
   }
   
   // 只返回自定义字段的写法
   attributes: [
     sequelize.literal(`(SELECT brnc.brnc_name FROM brncs AS brnc WHERE brnc.brnc_id = ocst.brncBrncId) AS brnc_name`),
     sequelize.literal(`(SELECT brn1.brn1_name FROM brn1s AS brn1 WHERE brn1.brn1_id = ocst.brn1Brn1Id LIMIT 1) AS brn1_name`),
     sequelize.literal(`(SELECT brn2.brn2_name FROM brn2s AS brn2 WHERE brn2.brn2_id = ocst.brn2_id LIMIT 1) AS brn2_name`),
     sequelize.literal(`CASE blnc_condition WHEN '1' THEN 'NORMAL' WHEN '2' THEN 'BERMASALAH' ELSE 'MACET' END AS blnc_condition`)
   ]
   

2. 去掉别名的单引号
   你给brnc_name加了单引号AS 'brnc_name'，Sequelize会把这个带引号的字符串当作字段名，导致返回的键名变成'brnc_name'（带引号），而不是预期的brnc_name。直接改成AS brnc_name即可。

3. 改用关联查询替代子查询（更优雅）
   如果你的Customer模型已经和Brnc、Brn1、Brn2定义了关联关系（比如belongsTo），直接用嵌套关联查询更符合ORM的设计，也能避免子查询的问题：

   include: [{
     model: db.Customer,
     required: true,
     include: [
       { model: db.Brnc, attributes: ['brnc_name'], required: true },
       { model: db.Brn1, attributes: ['brn1_name'], required: true },
       { model: db.Brn2, attributes: ['brn2_name'], required: true }
     ]
   }]
   

调试技巧 #

在findAndCountAll的配置里加上logging: console.log，Sequelize会打印出生成的SQL语句，和你手动在MySQL里执行的SQL对比，就能快速看出字段别名、查询结构是否有差异。

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