Select in the Sequelize

Question

I am trying to query the sequelize but I am not succeeding. I need to search for a collaborator in the bank by license plate. However, the employee must be from the same sector as the manager logged in to the system. The manager’s setorId is req.user.id id

So is my code

await Colaborador.findOne({
            include: [{
              model: Cargo,
            },
            {
                model: Setor,
                where:{
                    setorId: req.user.setorId
                }
            }
             ]
          },{
              where:{
                  matricula: matriculaId
              }
          })

I also need to do an Inner Join with the position and sector tables to appear on the screen. The sequelize is not returning me this wanted employee. What would be my mistake?

I managed to do by pure SQL, but I would like to do by Sequelize same.

With pure sql gets like this:

let colaborador = await sequelize.query(`SELECT c.colaboradorId, c.nome as nomeColaborador, g.nome as nomeCargo ,s.nome as nomeSetor FROM colaborador c, setor s, cargo g WHERE c.setorId = s.setorId AND  c.cargoId = g.cargoId AND c.setorId = ${req.user.setorId} AND c.matricula = ${matriculaId} LIMIT 1`, { type: QueryTypes.SELECT });

Answer 1

To understand how the find from Sequelize, I’ll explain equivalent to your SQL code:

SELECT c.colaboradorId, c.nome as nomeColaborador, g.nome as nomeCargo ,s.nome as nomeSetor
    FROM colaborador c, setor s, cargo g
    WHERE c.setorId = s.setorId
        AND c.cargoId = g.cargoId
        AND c.setorId = ${req.user.setorId}
        AND c.matricula = ${matriculaId}
    LIMIT 1

Stages

1. LIMIT 1: You did it correctly, the function findOne() of Sequelize serves for this.

2. Attributes: if you do not want a SELECT *, you must specify the attributes in an array attributes, and the attributes of associations (Cargo and Setor) must be defined in the object of include, for example:

Colaborador.findOne({
    attributes: ['attr1', 'attr2'],
    include: [{
        model: Modelo1,
        attributes: ['attr1_do_modelo1']
    }]
});

3. JOIN: In your SQL, you represented WHERE c.setorId = s.setorId AND c.cargoId = g.cargoId, which we can replace with a JOIN. The JOIN is held in the include of Sequelize, where the ORM identifies through the Foreign Key declared in the model by which attribute to look for, so it looks like this:

Colaborador.findOne({
    include: [{
        // Aqui o Sequelize já sabe que o Setor está associado ao Colaborador
        // pelos atributos Colaborador.setorId e Setor.setorId
        model: Setor
    }]
});

4. WHERE: The code for the WHERE c.setorId = ${req.user.setorId} AND c.matricula = ${matriculaId}. You can use a where common of find:

Colaborador.findOne({
    where: {
        setorId: req.user.setorId,
        matricula: matriculaId
    }
});

Upshot

Colaborador.findOne({
    attributes: ['colaboradorId', 'nome'],
    include: [
        {
            model: Cargo,
            attributes: ['nome']
        },
        {
            model: Setor,
            attributes: ['nome']
        }
    ],
    where: {
        setorId: req.user.setorId,
        matricula: matriculaId
    }
});