mysql 将sql查询转换为sequelize

dzjeubhm  于 2022-10-31  发布在  Mysql
关注(0)|答案(1)|浏览(224)

我无法转换查询我编写了sql查询来进行sequelize。这是我要转换的查询;

SELECT
sum(document_details.qty)'miktar',
stocks.id,
stocks.`name`
FROM
document_details
INNER JOIN documents ON documents.id = document_details.document_id
INNER JOIN stocks ON document_details.stock_id = stocks.id
WHERE documents.warehouse_id = 1
GROUP BY document_details.stock_id

这是我编写的查询。但是,我只得到一个数据。输出必须在多个

const {id} = req.query;
        const {limit, page, sortColumn, sortType, search} = req.body;
        Documents.hasMany(DocumentsDetail, {foreignKey: "document_id"})
        DocumentsDetail.belongsTo(Stocks, {foreignKey: "stock_id"});

        const DocumentsList = await Documents.findAll({
            limit: limit,
            offset: (page - 1) * limit,
            order: [
                [sortColumn, sortType]
            ],
            where: {
                [Op.and]: [
                    {
                        warehouse_id: id
                    },
                    {
                        id: {
                            [Op.substring]: [
                                search
                            ]
                        }
                    },
                ],
            },
            include: [
                {
                    model: DocumentsDetail,
                    attributes: [
                        "stock_id",
                        "name",
                        [sequelize.fn('sum', sequelize.col('qty')), 'total'],
                    ],
                    group: ["stock_id"]
                },
            ],

        });
        res.json({
            total: total.length,
            data: DocumentsList,
            warehouse_name
        });

我该怎么解决?或者真相是什么?你能帮忙吗?

u5rb5r59

u5rb5r591#

这就是我如何解决我的问题的。希望它也能帮助其他人。

DocumentsDetail.belongsTo(Documents, {foreignKey: "document_id"})
        DocumentsDetail.belongsTo(Stocks, {foreignKey: "stock_id"});

        await DocumentsDetail.findAll({
            attributes: [
                [sequelize.fn('sum', sequelize.col('qty')), 'quantity'],
            ],
            where: {
                "$document.warehouse_id$": id,
            },
            include: [
                {
                    model: Documents,
                },
                {
                    model: Stocks,
                },
            ],
            group: ["stock_id"],
        }).then((data) => {
            let opt = {
                total: data.length,
                data: warehouse_name,
                warehouse: data
            }
            res.json(opt);
        })

相关问题