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.