Sequelize嵌套结构中按orderMicrobial分组并聚合数量

问题需求 #

我希望简化Sequelize查询的返回结构，按照orderMicrobial中的字段进行分组，并对分组后的quantity字段求和，最终得到指定格式的结果。

期望结果 #

{
    "pendingInfo": [
        {
            "testTypeID": "1",
            "testType": "Breathing Air",
            "quantity": 5
        },
        {
            "testTypeID": "2",
            "testType": "Manufacturing",
            "quantity": 5
        },
        {
            "testTypeID": "3",
            "testType": "Microbial w/o spares",
            "quantity": 3
        },
        {
            "testTypeID": "3",
            "testType": "Microbial sealed pack",
            "quantity": 5
        },
        {
            "testTypeID": "3",
            "testType": "Microbial w/ spares",
            "quantity": 2
        }
    ]
}

当前返回结果 #

{
    "pendingInfo": [
        {
            "testTypeID": "1",
            "testType": "Breathing Air",
            "orders": [
                {
                    "quantity": 1,
                    "orderMicrobial": null
                },
                {
                    "quantity": 3,
                    "orderMicrobial": null
                },
                {
                    "quantity": 1,
                    "orderMicrobial": null
                }
            ]
        },
        {
            "testTypeID": "2",
            "testType": "Manufacturing",
            "orders": [
                {
                    "quantity": 5,
                    "orderMicrobial": null
                }
            ]
        },
        {
            "testTypeID": "3",
            "testType": "Microbial",
            "orders": [
                {
                    "quantity": 3,
                    "orderMicrobial": {
                        "hasSealedPlatePacks": null,
                        "hasSpares": null
                    }
                },
                {
                    "quantity": 4,
                    "orderMicrobial": {
                        "hasSealedPlatePacks": true,
                        "hasSpares": null
                    }
                },
                {
                    "quantity": 1,
                    "orderMicrobial": {
                        "hasSealedPlatePacks": true,
                        "hasSpares": null
                    }
                },
                {
                    "quantity": 2,
                    "orderMicrobial": {
                        "hasSealedPlatePacks": null,
                        "hasSpares": true
                    }
                }
            ]
        }
    ]
}

现有查询代码 #

const categorySummary = await TestType.findAll({
    include: [{
      model: Order,
      as: 'orders',
      include: [{
        model: OrderMicrobial,
        attributes: ['hasSealedPlatePacks', 'hasSpares']
      }],
      attributes: ['quantity']
    }],
    attributes: [
      ['testtypeID', 'testTypeID'],
      ['testtypeName', 'testType']
    ]
})

解决方案 #

方案一：通过Sequelize查询直接实现（推荐）

利用Sequelize的聚合函数和CASE语句，直接从数据库层面完成分组求和与类型名称映射：

const { sequelize } = require('./your-sequelize-instance'); // 替换为你的Sequelize实例路径

const pendingInfo = await Order.findAll({
    attributes: [
        ['testtypeID', 'testTypeID'],
        [
            sequelize.literal(`
                CASE
                    WHEN "orderMicrobial"."hasSealedPlatePacks" IS TRUE THEN 'Microbial sealed pack'
                    WHEN "orderMicrobial"."hasSpares" IS TRUE THEN 'Microbial w/ spares'
                    WHEN "TestType"."testtypeName" = 'Microbial' THEN 'Microbial w/o spares'
                    ELSE "TestType"."testtypeName"
                END
            `),
            'testType'
        ],
        [sequelize.fn('SUM', sequelize.col('quantity')), 'quantity']
    ],
    include: [
        {
            model: TestType,
            attributes: []
        },
        {
            model: OrderMicrobial,
            attributes: []
        }
    ],
    group: [
        'testtypeID',
        sequelize.literal(`
            CASE
                WHEN "orderMicrobial"."hasSealedPlatePacks" IS TRUE THEN 'Microbial sealed pack'
                WHEN "orderMicrobial"."hasSpares" IS TRUE THEN 'Microbial w/ spares'
                WHEN "TestType"."testtypeName" = 'Microbial' THEN 'Microbial w/o spares'
                ELSE "TestType"."testtypeName"
            END
        `)
    ],
    raw: true
});

const result = { pendingInfo };

方案二：查询后用JavaScript处理结果

如果数据库查询逻辑过于复杂，可先获取原始数据，再通过JS完成分组求和：

// 执行现有查询，开启raw和nest确保结构清晰
const categorySummary = await TestType.findAll({
    include: [{
      model: Order,
      as: 'orders',
      include: [{
        model: OrderMicrobial,
        attributes: ['hasSealedPlatePacks', 'hasSpares']
      }],
      attributes: ['quantity']
    }],
    attributes: [
      ['testtypeID', 'testTypeID'],
      ['testtypeName', 'testType']
    ],
    raw: true,
    nest: true
});

// 分组求和逻辑
const groupMap = new Map();
categorySummary.forEach(testType => {
    const baseID = testType.testTypeID;
    const baseName = testType.testType;

    testType.orders.forEach(order => {
        let displayName = baseName;
        if (baseName === 'Microbial') {
            const microbial = order.orderMicrobial;
            if (microbial?.hasSealedPlatePacks) {
                displayName = 'Microbial sealed pack';
            } else if (microbial?.hasSpares) {
                displayName = 'Microbial w/ spares';
            } else {
                displayName = 'Microbial w/o spares';
            }
        }
        const groupKey = `${baseID}-${displayName}`;
        if (groupMap.has(groupKey)) {
            groupMap.get(groupKey).quantity += order.quantity;
        } else {
            groupMap.set(groupKey, {
                testTypeID: baseID,
                testType: displayName,
                quantity: order.quantity
            });
        }
    });
});

const result = { pendingInfo: [...groupMap.values()] };

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