如何将Sequelize查询结果按cart_id分组为指定格式？

问题背景 #

我是Node.js Express结合PostgreSQL的新手，编写了如下路由：

get: async (req, res, next) => {
       
        var cat = await sequelize.query(
            `
            SELECT 
o."id" as "cart_id", 
o."user_id", 
o."createdBy",
o."createdAt", 
o."updatedAt",
i."quantity",
pi."name",
pi."price"
FROM "Orders" AS o
Left join public."CartItem" i on  i."cart_id"=o."id"
Left join public."Item" pi on  pi."id"=i."product_id"
WHERE o."user_id"=${req.body.user_id}

            `
            ,
         { type: QueryTypes.SELECT,group:`i."cart_id"` })
            .catch(e => res.send(e))
        if (cat) {
            // console.log(cat.Orders)
            cat.map(e=> {
                console.log(e)
            })
            res.send(cat)
        }
    },

通过Postman调用该路由后，得到如下响应：

[
    {
        "cart_id": 9,
        "user_id": 1,
        "createdBy": null,
        "createdAt": "2022-11-11T09:51:47.968Z",
        "updatedAt": "2022-11-11T09:51:47.968Z",
        "quantity": 4,
        "name": "test1",
        "price": 12000
    },
    {
        "cart_id": 9,
        "user_id": 1,
        "createdBy": null,
        "createdAt": "2022-11-11T09:51:47.968Z",
        "updatedAt": "2022-11-11T09:51:47.968Z",
        "quantity": 4,
        "name": "test2",
        "price": 12000
    }
]

希望将响应整理为以下格式（按cart_id分组，注：原期望格式外层数组存在语法错误，修正为对象格式）：

{
    "cart_9": [
        {
            "user_id": 1,
            "createdBy": null,
            "createdAt": "2022-11-11T09:51:47.968Z",
            "updatedAt": "2022-11-11T09:51:47.968Z",
            "quantity": 4,
            "name": "test1",
            "price": 12000
        },
        {
            "user_id": 1,
            "createdBy": null,
            "createdAt": "2022-11-11T09:51:47.968Z",
            "updatedAt": "2022-11-11T09:51:47.968Z",
            "quantity": 4,
            "name": "test2",
            "price": 12000
        }
    ]
}

解决方案 #

1. 使用JavaScript reduce 处理查询结果 #

在获取查询结果后，通过reduce方法按cart_id分组，同时移除每个项中的cart_id字段：

get: async (req, res, next) => {
    try {
        const cat = await sequelize.query(
            `
            SELECT 
                o."id" as "cart_id", 
                o."user_id", 
                o."createdBy",
                o."createdAt", 
                o."updatedAt",
                i."quantity",
                pi."name",
                pi."price"
            FROM "Orders" AS o
            Left join public."CartItem" i on  i."cart_id"=o."id"
            Left join public."Item" pi on  pi."id"=i."product_id"
            WHERE o."user_id" = :userId
            `,
            { 
                type: QueryTypes.SELECT,
                replacements: { userId: req.body.user_id } // 避免SQL注入风险
            }
        );

        if (cat) {
            // 分组并转换格式
            const groupedResult = cat.reduce((acc, item) => {
                const key = `cart_${item.cart_id}`;
                // 解构移除cart_id字段
                const { cart_id, ...rest } = item;
                // 初始化分组数组
                if (!acc[key]) acc[key] = [];
                acc[key].push(rest);
                return acc;
            }, {});

            res.send(groupedResult);
        }
    } catch (e) {
        res.status(500).send(e); // 用状态码标识错误更规范
    }
},

2. 修复SQL注入风险 #

你原代码直接将req.body.user_id拼接进SQL语句，这会导致严重的SQL注入漏洞，必须使用Sequelize的replacements参数传递变量，如上述代码所示。

3. 可选：使用Sequelize关联模型（ORM风格） #

如果已定义模型关联，可避免手写SQL，直接通过模型查询并转换格式：
假设模型关联已定义：

// Order模型
const Order = sequelize.define('Order', {
    id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
    user_id: DataTypes.INTEGER,
    createdBy: DataTypes.INTEGER,
    createdAt: DataTypes.DATE,
    updatedAt: DataTypes.DATE
});

// CartItem模型
const CartItem = sequelize.define('CartItem', {
    cart_id: DataTypes.INTEGER,
    product_id: DataTypes.INTEGER,
    quantity: DataTypes.INTEGER
});

// Item模型
const Item = sequelize.define('Item', {
    id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true },
    name: DataTypes.STRING,
    price: DataTypes.INTEGER
});

// 建立关联
Order.hasMany(CartItem, { foreignKey: 'cart_id', sourceKey: 'id' });
CartItem.belongsTo(Item, { foreignKey: 'product_id', targetKey: 'id' });

查询代码可改写为：

get: async (req, res, next) => {
    try {
        const orders = await Order.findAll({
            where: { user_id: req.body.user_id },
            include: [
                {
                    model: CartItem,
                    include: [Item]
                }
            ]
        });

        // 转换为目标格式
        const groupedResult = orders.reduce((acc, order) => {
            const key = `cart_${order.id}`;
            const items = order.CartItems.map(cartItem => ({
                user_id: order.user_id,
                createdBy: order.createdBy,
                createdAt: order.createdAt,
                updatedAt: order.updatedAt,
                quantity: cartItem.quantity,
                name: cartItem.Item.name,
                price: cartItem.Item.price
            }));
            acc[key] = items;
            return acc;
        }, {});

        res.send(groupedResult);
    } catch (e) {
        res.status(500).send(e);
    }
},

内容的提问来源于stack exchange，提问作者نعمان منذر محمود الجميلي