列users.role

当我按role_name列排序时，会发生此错误：

列users.role_name不存在

其余字段排序没有问题。如果按另一列排序，则为结果。

{
    "count": 1,
    "rows": [
        {
            "user_id": 1,
            "full_name": "Admin",
            "username": "admin",
            "password": "$2a$10$hAGEHofDTtJ9KZHSDbdhLe1ouKnoNcg7xSosaZrDT/ENVr4m9LAV.",
            "photosrc": "",
            "createdAt": "2019-10-11T05:55:26.114Z",
            "updatedAt": "2019-10-11T05:55:26.114Z",
            "roles_id": 1,
            "role": {
                "role_name": "Administrator"
            }
        }
    ]
}

网址请求：

/ api / users / get_all？search =＆sort = role_name＆order = asc＆pageSize = 10＆page = 1

请求：

module.exports.getAll = async function (req, res) {
    try {
        const query = {
            where: {
                [Op.or]: [
                    {
                        full_name: {
                            [Op.iLike]: `%${req.query.search}%`
                        }
                    },
                    {
                        username: {
                            [Op.like]: `%${req.query.search}%`
                        }
                    },
                    Sequelize.where(Sequelize.col((Roles, "role_name"), "varchar"), {
                        [Op.like]: `%${req.query.search}%`
                    })
                ]
            },
            include: [{ model: Roles, attributes: ["role_name"], required: true }],
            offset: +req.query.pageSize * (+req.query.page - 1),
            limit: +req.query.pageSize,
            order: [
                [req.query.sort, req.query.order]
            ]
        };
        const users = await Users.findAndCountAll(query);
        res.status(200).json(users);
    } catch (e) {
        errorHandler(res, e);
    }
};

由于您具有Roles的关系表，因此无法直接从Users模型中对其进行排序。

作为Users模型的order子句仅适用于Users模型的属性/列。

如果要基于Role_Name属性进行排序，则还必须根据所需条件在order中包括include子句。

类似这样的东西：

      const query = {
            where: {
                [Op.or]: [
                    {
                        full_name: {
                            [Op.iLike]: `%${req.query.search}%`
                        }
                    },
                    {
                        username: {
                            [Op.like]: `%${req.query.search}%`
                        }
                    },
                    Sequelize.where(Sequelize.col((Roles, "role_name"), "varchar"), {
                        [Op.like]: `%${req.query.search}%`
                    })
                ]
            },
            include: [{ model: Roles,
                attributes: ["role_name"],
                required: true,
                order: [
                  [req.query.sort, req.query.order]
                ]
            }],
            offset: +req.query.pageSize * (+req.query.page - 1),
            limit: +req.query.pageSize,
            order: [
                [req.query.sort, req.query.order]
            ]
        };

列users.role

当我按role_name列排序时，会发生此错误：

列users.role_name不存在

其余字段排序没有问题。如果按另一列排序，则为结果。

{
    "count": 1,
    "rows": [
        {
            "user_id": 1,
            "full_name": "Admin",
            "username": "admin",
            "password": "$2a$10$hAGEHofDTtJ9KZHSDbdhLe1ouKnoNcg7xSosaZrDT/ENVr4m9LAV.",
            "photosrc": "",
            "createdAt": "2019-10-11T05:55:26.114Z",
            "updatedAt": "2019-10-11T05:55:26.114Z",
            "roles_id": 1,
            "role": {
                "role_name": "Administrator"
            }
        }
    ]
}

网址请求：

/ api / users / get_all？search =＆sort = role_name＆order = asc＆pageSize = 10＆page = 1

请求：

module.exports.getAll = async function (req, res) {
    try {
        const query = {
            where: {
                [Op.or]: [
                    {
                        full_name: {
                            [Op.iLike]: `%${req.query.search}%`
                        }
                    },
                    {
                        username: {
                            [Op.like]: `%${req.query.search}%`
                        }
                    },
                    Sequelize.where(Sequelize.col((Roles, "role_name"), "varchar"), {
                        [Op.like]: `%${req.query.search}%`
                    })
                ]
            },
            include: [{ model: Roles, attributes: ["role_name"], required: true }],
            offset: +req.query.pageSize * (+req.query.page - 1),
            limit: +req.query.pageSize,
            order: [
                [req.query.sort, req.query.order]
            ]
        };
        const users = await Users.findAndCountAll(query);
        res.status(200).json(users);
    } catch (e) {
        errorHandler(res, e);
    }
};

由于您具有Roles的关系表，因此无法直接从Users模型中对其进行排序。

作为Users模型的order子句仅适用于Users模型的属性/列。

如果要基于Role_Name属性进行排序，则还必须根据所需条件在order中包括include子句。

类似这样的东西：

      const query = {
            where: {
                [Op.or]: [
                    {
                        full_name: {
                            [Op.iLike]: `%${req.query.search}%`
                        }
                    },
                    {
                        username: {
                            [Op.like]: `%${req.query.search}%`
                        }
                    },
                    Sequelize.where(Sequelize.col((Roles, "role_name"), "varchar"), {
                        [Op.like]: `%${req.query.search}%`
                    })
                ]
            },
            include: [{ model: Roles,
                attributes: ["role_name"],
                required: true,
                order: [
                  [req.query.sort, req.query.order]
                ]
            }],
            offset: +req.query.pageSize * (+req.query.page - 1),
            limit: +req.query.pageSize,
            order: [
                [req.query.sort, req.query.order]
            ]
        };