如何在 sequelize 的子子模型中添加条件，这会影响我在 findAndCountAll 中的父模型?

2022-01-19 00:00:00 node.js express mysql sequelize.js sequelize-cli

在 sequelize 中，我想在子子模型中的 findAndCountAll 调用中添加一个条件.如果条件为假，它应该影响父模型，findAndCountAll 应该返回数组长度 0.目前，只有子子子模型的数组长度为 0，其余的父模型正在返回.

In sequelize, I want to add a condition in findAndCountAll call in sub-sub child models. If the condition is false, it should impact on parent models and findAndCountAll should return array length 0. Currently, just that sub-sub child model's array length gets 0 and the rest of parents are being returned.

我在分享的代码中添加了更多细节.我的代码是这样的:

I have added more details in the code I am sharing. My code is like this:

    const { limit, offset } = dbHelpers.GetPagination(
        req.query.limit,
        req.query.offset
    );
    
    const results = await models.ModelA.findAndCountAll({
        where: condition,
        distinct: true,
        limit,
        offset,
        order: [["id", "DESC"]],
        include: [
            {
                model: models.ModelB,
                as: "ModelB",
                include: [
                    {
                        model: models.ModelC,
                        separate: true,
                    },
                    {
                        model: models.ModelD,
                        separate: true,
                        include: [
                            {
                                model: models.ModelE,
                                separate: true,
                                    {
                                        model: models.ModelF,
                                        where: "I want to add my condition here which should impact the whole query if not found just like the condition I have added in ModelG",
                                        separate: true,
                                    },
                                ],
                            },
                        ],
                    },
                ],
            },
            {
                model: models.ModelG,
                where: "I have added a condition here and if nothing is found, my parent query returns nothing which is exactly what I want.",
                include: [
                    {
                        model: models.ModelH,
                        as: "ModelH",
                    },
                ],
            },
        ],
    });

推荐答案

好的，所以我想出了解决方案，但添加了一个原始查询，其中我在需要的地方添加了所需的条件并获取了主模型的 id.然后我使用 sequelize 在 where 子句中添加这些 id 来获取数据.我的代码现在看起来像这样.

Okay so I figured the solution but adding a raw query with Joins in which I added my required conditions wherever I needed and fetched id's of the main model. Then I fetched the data using sequelize adding those ids in where clause. My code looks like this now.

const { limit, offset } = dbHelpers.GetPagination(
    req.query.limit,
    req.query.offset
);

let querySting = "(SELECT count(DISTINCT p.id) FROM ModelA p " +
    "JOIN ModelB cp ON cp.pId = p.id " +
    "JOIN ModelC cs ON cs.cpId = cp.id " +
    "JOIN ModelD ms ON ms.csId = cs.id " +
    "LEFT JOIN ModelE sa ON sa.msId = ms.id " +
    "LEFT JOIN ModelF pp ON pp.msId = ms.id " +
    "LEFT JOIN ModelG sta ON sta.ppId = pp.id " +
    "LEFT JOIN ModelH ol ON ol.cdId = cd.id " +
    "WHERE "   //you can add your conditions here
    + " ORDER BY p.id desc LIMIT  " + offset + ",  " + limit + ")"


const rawQuery = await models.sequelize.query(querySting,
    { type: QueryTypes.SELECT })
const Ids = rawQuery.map(result => result.id)

const results = await models.ModelA.findAndCountAll({
    where: {
        id: Ids
    },
    include: [
        {
            model: models.ModelB,
            as: "ModelB",
            include: [
                {
                    model: models.ModelC,
                    separate: true,
                },
                {
                    model: models.ModelD,
                    separate: true,
                    include: [
                        {
                            model: models.ModelE,
                            separate: true,
                                            {
                            model: models.ModelF,
                            separate: true,
                        },
                    ],
                },
            ],
        },
    ],
},
    {
        model: models.ModelG,
        include: [
            {
                model: models.ModelH,
                as: "ModelH",
            },
        ],
    },
                ],
            });

相关文章