Many-to-many to many-to-many query

[KrisLau]

I have a junction table for my RBAC tables called RoleAccess which links the Role, Resource, and Permission tables. I'm having trouble trying to figure out how to query a role's permissions?

My tables:

export class Role extends Model {
  static table = 'role';
  static associations = {
    role_access: {type: 'has_many', foreignKey: 'role_id'},
  };

  @field('role_id') roleID;
  @field('title') title;

  @lazy
  roleResources = this.collections
    .get('resource')
    .query(Q.on('role_access', 'role_id', this.id));

  // here I'm trying to get the roleResourcePermission 
}

export class Resource extends Model {
  static table = 'resource';
  static associations = {
    role_access: {type: 'has_many', foreignKey: 'resource_id'},
  };

  @field('resource_id') resourceID;
  @field('description') description;
}

export class Permission extends Model {
  static table = 'permission';
  static associations = {
    role_access: {type: 'has_many', foreignKey: 'permission_id'},
  };

  @field('permission_id') permissionID;
  @field('description') description;
}

export class RoleAccess extends Model {
  static table = 'role_access';
  static associations = {
    role: {type: 'belongs_to', key: 'role_id'},
    resource: {type: 'belongs_to', key: 'resource_id'},
    permission: {type: 'belongs_to', key: 'permission_id'},
  };

  @immutableRelation('role', 'role_id') role;
  @immutableRelation('resource', 'resource_id') resource;
  @immutableRelation('permission', 'permission_id') permission;
}

I was hoping to create a query that would be return the resource's permissions available to a role :

Role	Resource	Permission
admin	event	Create
admin	event	Read
admin	event	Update
admin	event	Delete

[KrisLau]

So in case anyone looks at this in the future, I basically had to query the role access first so something like:

await database.get('role_access').query(
    Q.where('role', 'admin'),
    Q.where('resource', 'event')
);