Sequelize: Select all where value is the highest

Question

I have a many-to-many relationship between Users and Roles. Since each User can have more than one role, I created a field, in the Role table, called "clearance", that can be 0 to 3, which is a number that represents how high the Role is in the hierarchy. There is a point in my server where I need to have the Users to include only the Role with the highest clearance. I've tried multiple uses of sequelize.fn('MAX', sequelize.col('clearance") in where, include and through objects, from Sequelize, to no avail. I am no expert in mysql nor in Sequelize, but it's the best js module I found to work as an ORM for my DB.

Answer 1

You could try something like this (no guarantee but looks like very close to what you want to achieve):

const users = await Users.findAll({
  include: [{
    model: Roles,
    limit: 1,
    order: ['clearance', 'DESC']
  }]
})