Sequelize - How to query for an indirect association?

Question

In my database I have Users. A user can have many Roles. A Role can belong to many Activities and Activities can belong to many Roles. A Role being associated with an Activity means any User with that Role can perform that activity.

Using Sequelize, and given a specific activity ID, what's the best way to query to see if a User instance has at least one Role that has permission to do the activity with that ID, and then return some kind of true/false response so I know if they are authorized to perform an Activity before proceeding?

If it were a raw query, it would probably look like this:

SELECT EXISTS(SELECT * 
                FROM "Activity_Role" 
               WHERE "ActivityId" = 'READ_FILE' 
                 AND "RoleId" = ANY(SELECT "RoleId" 
                                      FROM "User_Role" 
                                     WHERE "UserId" = '12321') 
               LIMIT 1);

Is there a way to do this without resorting to a raw query?

Keval Gohil Keval Gohil · Accepted Answer · 2017-02-07T07:54:29

Try nested query with where clause

User.findAll({
       include: [{
        model: Role,
        include : [{model : Activity, where : {id : activityID}}]
       }]
    })

Sequelize - How to query for an indirect association?

1 Answers