Multitable relationship in bookshelf.js

Question

I am attempting to implement role based access control in a node.js app using bookshelf.js as an ORM to Postgresql. I have the following schema:

USERS <- USERS_ROLES -> ROLES <- ROLES_RIGHTS -> RIGHTS

I'd like to be able to get all of the rights owned by a user. In sql, I would just do this:

SELECT rights.*
FROM rights
INNER JOIN roles_rights ON roles_rights.right_id=rights.id
INNER JOIN users_roles ON users_roles.role_id = roles_rights.role_id
WHERE users_roles.user_id=1
GROUP BY rights.id

Being relatively new to Bookshelf.js, I'm having some trouble figuring out how to set up the relation. Here was my first attempt:

const User = bookshelf.Model.extend({

  tableName: 'users',

  roles: function() {
    return this.belongsToMany(Role, 'users_roles', 'user_id', 'role_id')
  },

  rights: function() {
    return this.belongsToMany(Right, 'users_roles', 'user_id', 'role_id')
      .query(qb => qb
        .innerJoin('roles_rights', 'roles_rights.right_id', 'rights.id')
      )
  }

})

const Role = bookshelf.Model.extend({

  tableName: 'roles',

  rights: function() {
    return this.belongsToMany(Right, 'roles_rights', 'role_id', 'right_id')
  },

  users: function() {
    return this.belongsToMany(User, 'users_roles', 'user_id', 'role_id')
  }

})

const Right = bookshelf.Model.extend({

  tableName: 'rights',

  roles: function() {
    return this.belongsToMany(Role, 'users_roles', 'user_id', 'role_id')
  }

})

It did not work... :-(

Ultimately, I want to make this query work:

User.where({ id: req.user.get('id') })
  .fetch({ withRelated: ['rights'] })
  .then(user => {
    ...
  })
})

Any help would be greatly appreciated!

Answer 1

After struggling with the same issue for some time, this is the closest I have gotten to a usable solution.

Without any modifications to the models that you have described (except removing the rights relation from the User model), you can do this:

User.where({ id: req.user.get('id') })
  .fetch({ withRelated: ['roles.rights'] })
  .then(user => {
    console.log(JSON.stringify(user));
  })
})

I have found this to be a better for my use case, as I will always have the user's role available and the roles' rights inside it.

Something like this:

{
  id: 12
  roles: [
    {
      id: 21,
      rights: [
        {
          id: 11,
          name: 'DELETE'
        }
      ]
    }
  ]
}

If you do want all the rights that the user has combined from all the user's roles, you can use Bookshelf's virtuals plugin and make a virtual row.

const User = bookshelf.Model.extend({

  tableName: 'users',

  roles: function() {
    return this.belongsToMany(Role, 'users_roles', 'user_id', 'role_id')
  },

  virtuals: {
    rights: {
      get: function () {
        const rightsIds = new Set();
        return this.related('roles').reduce((rights, group) => {
          return group.related('rights').reduce((rights, right) => {
            if (!rightsIds.has(right.id)) {
              rightsIds.add(right.id);
              rights.push(right);
            }
            return rights;
          }, rights);
        }, []);
      }
    }
  },
});

Use it like this:

User.where({ id: req.user.get('id') })
  .fetch({ withRelated: ['roles.rights'] })
  .then(user => {
    console.log(JSON.stringify(user.get('rights'));
  })
})

The virtuals plugin must be enabled when initializing bookshelf:

bookshelf.plugin(['virtuals']);

This is not the most elegant of solutions, but if you want to do everything with bookshelf, this is it. I just use knex whenever bookshelf lets me down (and that happens a lot).