Skip to content

setup model relationships #2

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

    Fields

    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions