How to sort by column?

Viewed 72

When I sort by role_name column, this error occurs:

column users.role_name does not exist

If sort by any other column, then there will be no problems. How to sort by column for which join is performed?

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);
    }
};

If add to include:

order: [
    [Roles, req.query.sort, req.query.order]
]

then it does not work. And if add this after include, it will not be possible to sort by the remaining columns.

0 Answers
Related