How to write nested queries using sequelize in nodejs

Question

I'm trying to write nested queries using sequelize of this query.


const userIdPriorityLevel = await db.sequelize.query(
            `select "p_level" from roles  where id=(select "roleId" from users where id='${userId}');`
            , { raw: true });

the above query works like this

user table

id   roleId
1     2
2     3

roles table

id   p_level
2     4324
3     3423

please help how to do that.

updated:


        const userIdPriorityLevel = await db.Role.findAll({
            attributes: ['p_level'],
            include: [{
                model: db.User,
                required: true,
                attributes: [],
                where: {
                    id: userId
                }
            }
            ]
        })

        console.log("userid   ", userIdPriorityLevel)

when i print the above query it gives me this output

userid    [
  role {
    dataValues: { p_level: 700 },
    _previousDataValues: { p_level: 700 },
    _changed: Set(0) {},
    _options: {
      isNewRecord: false,
      _schema: null,
      _schemaDelimiter: '',
      include: [Array],
      includeNames: [Array],
      includeMap: [Object],
      includeValidated: true,
      attributes: [Array],
      raw: true
    },
    isNewRecord: false
  }
]

i want to extract only this p_level: 700 value from above output. trying something like this

  const userPriorityLevel = userIdPriorityLevel[0][0]['p_level']

but it is not working.

What exactly do you want? Rewrite this plain SQL query to a Sequelize one? Or just correct it somehow? — Anatoly
– Anatoly, Commented Jan 27, 2022 at 14:40
yes, i did this User.belongsTo(Role) where user and role both are tables. — TimeToCode
– TimeToCode, Commented Feb 1, 2022 at 10:02

Anatoly · Accepted Answer · 2022-02-01 12:58:53Z

1

Considering you also have Role.hasMany(User) association you can try something like this:

const roles = await Roles.findAll({
  attributes: ['p_level'],
  include: [{
    model: User,
    required: true,
    attributes: [],
    where: {
      id: userId
    }
  }
  ]
})

Update: to get plain objects from Sequelize query call get({ plain: true }) for each model instance:

const plainRoles = roles.map(x => x.get({ plain: true }))

edited Feb 1, 2022 at 12:58

answered Feb 1, 2022 at 12:05

Anatoly

23.2k3 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TimeToCode Over a year ago

it works, thanks. but when i want the p_level value from its output, please check my updated question.

Anatoly Over a year ago

see updated answer

Collectives™ on Stack Overflow

How to write nested queries using sequelize in nodejs

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related