Skip to content

setup model relationships #2

New issue
New issue
Open
Open
setup model relationships#2
@hashwarp

Description

@hashwarp
hashwarp
opened on Aug 21, 2024

convert the old objection postgres schemas to mongoose.

using this objectionjs for postgres schema, make sure this mongoose schema has the right relationships:

// objection.js

  public static get relationMappings(): RelationMappings {
    return {
      users: {
        relation: Model.ManyToManyRelation,
        modelClass: Profile,
        join: {
          from: 'permissions.id',
          to: 'profiles.id',
          through: {
            from: 'profiles_permissions.permissionId',
            to: 'profiles_permissions.userId'
          }
        }
      },
      roles: {
        relation: Model.ManyToManyRelation,
        modelClass: Role,
        join: {
          from: 'permissions.id',
          to: 'roles.id',
          through: {
            from: 'roles_permissions.permissionId',
            to: 'roles_permissions.roleId'
          }
        }
      }
    }
  }

// mongoose.ts


export const Permission = new Schema<types.Permission>(
  {
    name: { type: String, required: true, maxlength: 100 },
    applicationId: { type: Schema.Types.ObjectId, ref: 'Application', required: true },
    ownerId: { type: Schema.Types.ObjectId, ref: 'Profile' },
    createdById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    editedById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    deletedById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    createdDate: { type: Date, default: Date.now },
    updatedDate: { type: Date },
    deletedDate: { type: Date },
    permissionsOnRoles: [{ type: Schema.Types.ObjectId, ref: 'PermissionsOnRoles' }],
  },
  {
    timestamps: { createdAt: 'createdDate', updatedAt: 'updatedDate' },
    collection: 'Permission',
  }
);

result

import { Schema } from 'mongoose';
import * as types from './types'; // Adjust import as per your structure

export const Permission = new Schema<types.Permission>(
  {
    name: { type: String, required: true, maxlength: 100 },
    applicationId: { type: Schema.Types.ObjectId, ref: 'Application', required: true },
    ownerId: { type: Schema.Types.ObjectId, ref: 'Profile' },
    createdById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    editedById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    deletedById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    createdDate: { type: Date, default: Date.now },
    updatedDate: { type: Date },
    deletedDate: { type: Date },
    
    // Many-to-Many relationships
    profiles: [{ type: Schema.Types.ObjectId, ref: 'Profile' }], // Reference to Profile for ManyToMany relation
    roles: [{ type: Schema.Types.ObjectId, ref: 'Role' }], // Reference to Role for ManyToMany relation

    // Explicitly handle relationships via join tables
    permissionsOnProfiles: [{ type: Schema.Types.ObjectId, ref: 'PermissionsOnProfiles' }],
    permissionsOnRoles: [{ type: Schema.Types.ObjectId, ref: 'PermissionsOnRoles' }],
  },
  {
    timestamps: { createdAt: 'createdDate', updatedAt: 'updatedDate' },
    collection: 'Permission',
  }
);

// You also need schemas for the join tables:
export const PermissionsOnProfiles = new Schema(
  {
    permissionId: { type: Schema.Types.ObjectId, ref: 'Permission', required: true },
    profileId: { type: Schema.Types.ObjectId, ref: 'Profile', required: true },
  },
  {
    collection: 'PermissionsOnProfiles',
  }
);

export const PermissionsOnRoles = new Schema(
  {
    permissionId: { type: Schema.Types.ObjectId, ref: 'Permission', required: true },
    roleId: { type: Schema.Types.ObjectId, ref: 'Role', required: true },
  },
  {
    collection: 'PermissionsOnRoles',
  }
);

but thats too many schemas, we need to use the "Node" schema as a bridge for relationships

create the relationships using this Node schema bridge:


export const Node = new Schema() < types.Node > <NodeDocument>({
    parentId: {
      type: Schema.Types.ObjectId,
      ref: 'Node',
      required: true,
    },
    relationKey: {
      type: String,
      required: true,
    },
    relationType: {
      type: String,
      required: true,
    },
    fromAccountId: {
      type: Schema.Types.ObjectId,
      ref: 'Account',
    },
    toAccountId: {
      type: Schema.Types.ObjectId,
      ref: 'Account',
    },
    fromProfileId: {
      type: Schema.Types.ObjectId,
      ref: 'Profile',
    },
    toProfileId: {
      type: Schema.Types.ObjectId,
      ref: 'Profile',
    },
    fromBadgeId: Schema.Types.ObjectId,
    toBadgeId: Schema.Types.ObjectId,
    fromAchievementId: Schema.Types.ObjectId,
    toAchievementId: Schema.Types.ObjectId,
    fromIdeaId: {
      type: Schema.Types.ObjectId,
      ref: 'Idea',
    },
    toIdeaId: {
      type: Schema.Types.ObjectId,
      ref: 'Idea',
    },
    fromSuggestionId: Schema.Types.ObjectId,
    toSuggestionId: Schema.Types.ObjectId,
    fromProjectId: {
      type: Schema.Types.ObjectId,
      ref: 'Project',
    },
    toProjectId: {
      type: Schema.Types.ObjectId,
      ref: 'Project',
    },
    fromProductId: {
      type: Schema.Types.ObjectId,
      ref: 'Product',
    },
    toProductId: {
      type: Schema.Types.ObjectId,
      ref: 'Product',
    },
    fromAssetId: Schema.Types.ObjectId,
    toAssetId: Schema.Types.ObjectId,
    fromBountyId: Schema.Types.ObjectId,
    toBountyId: Schema.Types.ObjectId,
    fromRealmId: Schema.Types.ObjectId,
    toRealmId: Schema.Types.ObjectId,
    fromCommunityId: {
      type: Schema.Types.ObjectId,
      ref: 'Community',
    },
    toCommunityId: {
      type: Schema.Types.ObjectId,
      ref: 'Community',
    },
    fromCollectionId: {
      type: Schema.Types.ObjectId,
      ref: 'Collection',
    },
    toCollectionId: {
      type: Schema.Types.ObjectId,
      ref: 'Collection',
    },
    fromDiscussionId: {
      type: Schema.Types.ObjectId,
      ref: 'Discussion',
    },
    toDiscussionId: {
      type: Schema.Types.ObjectId,
      ref: 'Discussion',
    },
    fromMessageId: Schema.Types.ObjectId,
    toMessageId: Schema.Types.ObjectId,
    fromOfferId: Schema.Types.ObjectId,
    toOfferId: Schema.Types.ObjectId,
    fromLicenseId: Schema.Types.ObjectId,
    toLicenseId: Schema.Types.ObjectId,
    fromOrderId: Schema.Types.ObjectId,
    toOrderId: Schema.Types.ObjectId,
    fromRatingId: Schema.Types.ObjectId,
    toRatingId: Schema.Types.ObjectId,
    fromReviewId: Schema.Types.ObjectId,
    toReviewId: Schema.Types.ObjectId,
    fromTagId: Schema.Types.ObjectId,
    toTagId: Schema.Types.ObjectId,
    fromVoteId: {
      type: Schema.Types.ObjectId,
      ref: 'Vote',
    },
    toVoteId: {
      type: Schema.Types.ObjectId,
      ref: 'Vote',
    },
    fromLeaderboardId: Schema.Types.ObjectId,
    toLeaderboardId: Schema.Types.ObjectId,
    fromLogId: Schema.Types.ObjectId,
    toLogId: Schema.Types.ObjectId,
    fromFileId: Schema.Types.ObjectId,
    toFileId: Schema.Types.ObjectId,
    fromEventId: Schema.Types.ObjectId,
    toEventId: Schema.Types.ObjectId,
    fromServerId: Schema.Types.ObjectId,
    toServerId: Schema.Types.ObjectId,
    createdDate: { type: Date, default: Date.now },
    updatedDate: { type: Date },
    deletedDate: { type: Date },
  },
  {
    timestamps: { createdAt: 'createdDate', updatedAt: 'updatedDate' },
    collection: 'Node',
  });

so give me the updated Permission, Role and Profile models


export const Profile = new Schema<types.Profile>(
  {
    metaverseId: {
      type: mongoose.Schema.Types.ObjectId,
      ref: 'Metaverse',
      required: true,
    },
    accountId: { type: mongoose.Schema.Types.ObjectId, ref: 'Account', required: true },
    name: { type: String, required: true },
    key: { type: String, maxlength: 100 },
    status: { type: String, default: 'Active', enum: ['Paused', 'Pending', 'Active', 'Archived'] },
    data: { type: Object, default: {} },
    meta: { type: Object, default: {} },
    createdDate: { type: Date, default: Date.now },
    updatedDate: { type: Date },
    deletedDate: { type: Date },
    points: { type: Number },
    coins: { type: Number },
    activityRating: { type: Number, default: 0 },
    address: { type: String, maxlength: 100 },
    avatar: { type: String, maxlength: 100 },
    roleId: { type: mongoose.Schema.Types.ObjectId, ref: 'Role' },
    privateKey: { type: String, maxlength: 300 },
    signature: { type: String, maxlength: 200 },
    createdById: { type: mongoose.Schema.Types.ObjectId, ref: 'Profile' },
    editedById: { type: mongoose.Schema.Types.ObjectId, ref: 'Profile' },
    deletedById: { type: mongoose.Schema.Types.ObjectId, ref: 'Profile' },
    chainId: { type: mongoose.Schema.Types.ObjectId, ref: 'Chain' },
  },
  { collection: 'Profile', timestamps: { createdAt: 'createdDate', updatedAt: 'updatedDate' } }
);


export const Role = new Schema<types.Role>(
  {
    name: { type: String, required: true, maxlength: 100 },
    key: { type: String, maxlength: 100 },
    value: { type: String },
    meta: { type: Object, default: {} },
    status: { type: String, default: 'Active', enum: ['Paused', 'Pending', 'Active', 'Archived'] },
    metaverseId: {
      type: mongoose.Schema.Types.ObjectId,
      ref: 'Metaverse',
      required: true,
    },
    ownerId: { type: Schema.Types.ObjectId, ref: 'Profile' },
    createdById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    editedById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    deletedById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    createdDate: { type: Date, default: Date.now },
    updatedDate: { type: Date },
    deletedDate: { type: Date },
    rolesOnProfiles: [{ type: Schema.Types.ObjectId, ref: 'RolesOnProfiles' }],
    permissionsOnRoles: [{ type: Schema.Types.ObjectId, ref: 'PermissionsOnRoles' }],
  },
  {
    timestamps: { createdAt: 'createdDate', updatedAt: 'updatedDate' },
    collection: 'Role',
  }
);


export const Permission = new Schema<types.Permission>(
  {
    name: { type: String, required: true, maxlength: 100 },
    applicationId: { type: Schema.Types.ObjectId, ref: 'Application', required: true },
    ownerId: { type: Schema.Types.ObjectId, ref: 'Profile' },
    createdById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    editedById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    deletedById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    createdDate: { type: Date, default: Date.now },
    updatedDate: { type: Date },
    deletedDate: { type: Date },
    permissionsOnRoles: [{ type: Schema.Types.ObjectId, ref: 'PermissionsOnRoles' }],
  },
  {
    timestamps: { createdAt: 'createdDate', updatedAt: 'updatedDate' },
    collection: 'Permission',
  }
);

import mongoose, { Schema } from 'mongoose';
import * as types from './types';

export const Profile = new Schema<types.Profile>(
  {
    metaverseId: {
      type: mongoose.Schema.Types.ObjectId,
      ref: 'Metaverse',
      required: true,
    },
    accountId: { type: mongoose.Schema.Types.ObjectId, ref: 'Account', required: true },
    name: { type: String, required: true },
    key: { type: String, maxlength: 100 },
    status: { type: String, default: 'Active', enum: ['Paused', 'Pending', 'Active', 'Archived'] },
    data: { type: Object, default: {} },
    meta: { type: Object, default: {} },
    createdDate: { type: Date, default: Date.now },
    updatedDate: { type: Date },
    deletedDate: { type: Date },
    points: { type: Number },
    coins: { type: Number },
    activityRating: { type: Number, default: 0 },
    address: { type: String, maxlength: 100 },
    avatar: { type: String, maxlength: 100 },
    roleId: { type: mongoose.Schema.Types.ObjectId, ref: 'Role' },
    privateKey: { type: String, maxlength: 300 },
    signature: { type: String, maxlength: 200 },
    createdById: { type: mongoose.Schema.Types.ObjectId, ref: 'Profile' },
    editedById: { type: mongoose.Schema.Types.ObjectId, ref: 'Profile' },
    deletedById: { type: mongoose.Schema.Types.ObjectId, ref: 'Profile' },
    chainId: { type: mongoose.Schema.Types.ObjectId, ref: 'Chain' },
  },
  { collection: 'Profile', timestamps: { createdAt: 'createdDate', updatedAt: 'updatedDate' } }
);

// Virtual relationship for roles
Profile.virtual('roles', {
  ref: 'Role',
  localField: '_id',
  foreignField: 'roleId',
  justOne: false,
});

// Virtual relationship for permissions
Profile.virtual('permissions', {
  ref: 'Permission',
  localField: '_id',
  foreignField: 'ownerId',
  justOne: false,
});

import mongoose, { Schema } from 'mongoose';
import * as types from './types';

export const Role = new Schema<types.Role>(
  {
    name: { type: String, required: true, maxlength: 100 },
    key: { type: String, maxlength: 100 },
    value: { type: String },
    meta: { type: Object, default: {} },
    status: { type: String, default: 'Active', enum: ['Paused', 'Pending', 'Active', 'Archived'] },
    metaverseId: {
      type: mongoose.Schema.Types.ObjectId,
      ref: 'Metaverse',
      required: true,
    },
    ownerId: { type: Schema.Types.ObjectId, ref: 'Profile' },
    createdById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    editedById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    deletedById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    createdDate: { type: Date, default: Date.now },
    updatedDate: { type: Date },
    deletedDate: { type: Date },
  },
  {
    timestamps: { createdAt: 'createdDate', updatedAt: 'updatedDate' },
    collection: 'Role',
  }
);

// Virtual relationship for profiles
Role.virtual('profiles', {
  ref: 'Profile',
  localField: '_id',
  foreignField: 'roleId',
  justOne: false,
});

// Virtual relationship for permissions
Role.virtual('permissions', {
  ref: 'Permission',
  localField: '_id',
  foreignField: 'permissionsOnRoles',
  justOne: false,
});

import mongoose, { Schema } from 'mongoose';
import * as types from './types';

export const Permission = new Schema<types.Permission>(
  {
    name: { type: String, required: true, maxlength: 100 },
    applicationId: { type: Schema.Types.ObjectId, ref: 'Application', required: true },
    ownerId: { type: Schema.Types.ObjectId, ref: 'Profile' },
    createdById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    editedById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    deletedById: { type: Schema.Types.ObjectId, ref: 'Profile' },
    createdDate: { type: Date, default: Date.now },
    updatedDate: { type: Date },
    deletedDate: { type: Date },
  },
  {
    timestamps: { createdAt: 'createdDate', updatedAt: 'updatedDate' },
    collection: 'Permission',
  }
);

// Virtual relationship for roles
Permission.virtual('roles', {
  ref: 'Role',
  localField: '_id',
  foreignField: 'permissionsOnRoles',
  justOne: false,
});

// Virtual relationship for profiles
Permission.virtual('profiles', {
  ref: 'Profile',
  localField: '_id',
  foreignField: 'ownerId',
  justOne: false,
});


Populating Relationships:

To fetch related documents, you would use Mongoose’s .populate() method:
Profile.findById(profileId).populate('roles').populate('permissions').exec();

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions