Nested inner join in sequalize PostgreSQL

Question

I want to display all users except who's role name is an admin trader and customer.

User table has: id, name, email and password

Role table has: id, role_name

RoleMapping has: id, user_id and role_id

here is my query

models.User.findAll({
      include: [{
            model: models.RoleMapping,
            as: 'user_role',
            attributes: ['user_id', 'role_id'],
            include: [{
                model: models.Role,
                as: 'Role',
                attributes: ['id', 'role_name', ],
            }]
        }],
        where :{
            '$Role.role_name$': {
                $notIn: ['Trader','Customer','admin']
            }

        }  
    })

getting this error error: missing FROM-clause entry for table "Role"

Chuong Tran Chuong Tran · Accepted Answer · 2019-03-07T07:10:38

Can you try? And let me know, I will try to update the answer. I don't test it

models.User.findAll({
  include: [{
        model: models.RoleMapping,
        as: 'user_role',
        attributes: ['user_id', 'role_id'],
        include: [{
            model: models.Role,
            as: 'Role',
            attributes: ['id', 'role_name', ],
            where :{
              role_name: {
                  $notIn: ['Trader','Customer','admin']
              }
          }
        }]
    }],  
})

Nested inner join in sequalize PostgreSQL

1 Answers