Need to create smart relationship between two collections that exist in different database with MongoDB

Hi Team, I need to now how to create a smart relationship between two collection that exist in different database.

My Config.
MongoDB Version: MongoDB 4.4.17 Enterprise. (doesn’t support lookup operation for joining the collection in the different database)
forest admin agent installed in NodeJs.
npm mongoose --version 8.1.0
npm express --version 8.1.0

Schema Sample:
Database Name: credit, Collection name: customers.
Database Name: loans, Collection_name: transactions

customers sample schema:
{
_id: Object_id,
company_name: String,
entity_id: ObjectId
}

transactions sample schema

{
_id: ObjectId,
customer_id: ObjectId
}

Relationship.

transactions collections customer_id belongs_to Customer collection entity_id.

Hello @Giri_S,

Thank you for your message :raised_hands:

Have you checked this part of the documentation?
If it doesn’t suit your needs, could you please tell me why?

Thanks!

Yes @adriguy I checked but i receiving the below error when I tried to create smart relationship.

Cannot retrieve the orginal_customer_id value because of an internal error in the getter implementation: Customer.find is not a function

customers is a model.
orginal_customer_id is a smart relationship field.

const { collection } = require("forest-express-mongoose");
const logger = require("../config/logger")(module);
const { loanTransactions } = require("../models");
const AWS = require('aws-sdk');
const Customer = require('../models/onboarding/customers');
console.log("Printing Customer", Customer)



// This file allows you to add to your Forest UI:
// - Smart actions: https://docs.forestadmin.com/documentation/reference-guide/actions/create-and-manage-smart-actions
// - Smart fields: https://docs.forestadmin.com/documentation/reference-guide/fields/create-and-manage-smart-fields
// - Smart relationships: https://docs.forestadmin.com/documentation/reference-guide/relationships/create-a-smart-relationship
// - Smart segments: https://docs.forestadmin.com/documentation/reference-guide/segments/smart-segments

collection("loanTransactions", {
  fields: [
    {
      field: 'orginal_customer_id',
      type: 'String',
      reference: 'customers._id',
      get: function (transaction) {
        return Customer.find({entity_id: transaction.customer_id});
      }
    }
  ],
  actions: [
   
    {
      name: "Release Deal",
      type: "single",
      fields: [

        {
          field: "deal_name",
          type: "String",
          isRequired: true,
          isReadOnly: true
        },
        {
          field: "investor_ids",
          isRequired: true,
          reference: "entities._id",
          type: ['String']
        }
      ], hooks: {
        load: async ({ fields, request }) => {
          const deal_name = fields.find(
            (field) => field.field === 'deal_name'
          );
          const id = request.body.data.attributes.ids[0];
          const loanTransaction = await loanTransactions.findById(id);
          deal_name.value = loanTransaction.deal_name;
          return fields;
        },
      },
    },
  ],

});


Customer schema

// This model was generated by Forest CLI. However, you remain in control of your models.
// Learn how here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models

module.exports = (mongoose, Mongoose) => {
  // This section contains the properties of your model, mapped to your collection's properties.
  // Learn more here: https://docs.forestadmin.com/documentation/v/v6/reference-guide/models/enrich-your-models#declaring-a-new-field-in-a-model
  const schema = Mongoose.Schema({
    'company_name': String,
    'state': String,
    'entity_id': { type: Mongoose.Schema.Types.ObjectId, ref: 'craEntities' },
    'last_updated_by': { type: Mongoose.Schema.Types.ObjectId, ref: 'craUsers' },
    'updated_at': Date,
    'created_at': Date,
    'created_by': String,
    'esop': {
      'esop_status': String,
      'vesting_period': [Number],
    },
  }, {
    timestamps: false,
  });

  return mongoose.model('customers', schema, 'customers');
};

Index.js

const fs = require('fs');
const path = require('path');
const Mongoose = require('mongoose');

const databasesConfiguration = require('../config/databases');

const connections = {};
const db = {};

databasesConfiguration.forEach((databaseInfo) => {
  const connection = Mongoose.createConnection(databaseInfo.connection.url, databaseInfo.connection.options);
  connections[databaseInfo.name] = connection;

  const modelsDir = databaseInfo.modelsDir || path.join(__dirname, databaseInfo.name);
  fs
    .readdirSync(modelsDir)
    .filter((file) => file.indexOf('.') !== 0 && file !== 'index.js')
    .forEach((file) => {
      try {
        const model = require(path.join(modelsDir, file))(connection, Mongoose);
        db[model.modelName] = model;
      } catch (error) {
        console.error(`Model creation error: ${error}`);
      }
    });
});

db.objectMapping = Mongoose;
db.connections = connections;

module.exports = db;

@Giri_S,

Thanks for the prompt reply.

Just to be sure: apart from this smart relationship, is your configuration on Forest Admin with multiple DB working fine? Can you browse all your collections?
(Otherwise you might want to check this topic)

About the error you’re facing, I’m not an expert of MongoDB 4 Enterprise.
Should the find be accessible?
Can you log the Customer value inside the getter to see if it’s well defined?

Thanks.

@adriguy Yes everything is working fine except this one smart-relationship field, even in the detailed view, I can see all fields, except this smart relationship field.

i logged the value: Printing Customer [Function (anonymous)]

MongoDB connections and methods are working fine.

my guess is since Customer schema is anonymous function, it expects two input to be passed, even the vscode text editor linter showing this as error(attaching a screenshot).

Hey @Giri_S :wave:

Could you try to import Customer as const { Customer } = require('../models');?

According to your models/index.js (db[model.modelName] = model;) all your models should be exposed as “flat”. If it still does not work, I would suggest to try:

  • Using the connection name to directly import the model
  • console.log(db) inside models/index.js to check if the model name is available.

Let me know if that helps :pray:

1 Like

Hey @jeffladiray solution you provided worked but now I am struggling in getting the _id field of any model, i can’t able to get the _id of any document. (attached the debug pointer screenshot for reference)

The get function should return a customer document were customers.entity_id == transactions.customer_id, can you please guide on how to write this.
@jeffladiray @adriguy

Shouldn’t it be transaction.customer_id instead of transaction.customer_id.id in your get function ?

thanks @jeffladiray i can able to define a relationship successfully now. but now when I try to include the related data in summary I can’t able to do that, since relation_data is undetectable.


@adriguy

So the initial issue is resolved, right?

For the second one, it will be easier to debug if you could provide the associated HTTP response of the summary view (Here or as DM if you consider it private)

@jeffladiray initial issue was resolved thanks a lot. for the second one below is the response

{"included":[{"type":"customers","id":"632022ed2cad2c001ecb0732","attributes":{"_id":"632022ed2cad2c001ecb0732"}},{"type":"entities","id":"632022eb93e12d006c4552d8","attributes":{"PAN":"AABCU9001R","registrar":null,"can_avail_moratorium":false,"subgroups":["anchor"],"company_name":"EF Builders","investor":null,"updated_at":"2022-09-13T17:16:29.356Z","created_at":"2022-09-13T06:27:56.385Z","trustee":null,"retail_user":null,"rating_agency":null,"customer":true,"CIN":null,"vendor":null,"contact_person":null,"law_firm":null,"auditor":null,"anchor":null,"collateral_manager":null,"guarantor":null,"_id":"632022eb93e12d006c4552d8"}}],"data":{"type":"loanTransactions","id":"637b7e423d671d001a0d191b","attributes":{"deal_name":"EF LAP 11 2022 T53","transaction_state":"draft","_id":"637b7e423d671d001a0d191b"},"relationships":{"borrower_id":{"data":{"type":"customers","id":"632022ed2cad2c001ecb0732"},"links":{"related":{"href":"/forest/loanTransactions/637b7e423d671d001a0d191b/relationships/borrower_id"}}},"customer_id":{"data":{"type":"entities","id":"632022eb93e12d006c4552d8"},"links":{"related":{"href":"/forest/loanTransactions/637b7e423d671d001a0d191b/relationships/customer_id"}}}}}}

From the screenshot you shared and the type of relation you declared (A belongsTo), nothing will be displayed in the “Related data” of the “Loan transactions” collection for the summary via (As a Loan Transactions only have one customer). Customer infos should be available in the “Fields” part of the Visual Builder.

Let me know if that helps.

@jeffladiray @adriguy Got it, I have one more use case, where I need to create a has_many smart relationship, while going through the doc they used the lookup operator but in my case, I can’t use the lookup operator because collections exist in different database and my version of mongo doesn’t support lookup between collections in a different database. kindly guide me in fixing this.

At first read and in my opinion, you should still be able to achieve this without lookup, as long as you have access to both table on both databases.

The following hasMany declaration in our forest-express-mongoose documentation available here doesn’t mention the lookup function.

Let me know if that helps.

1 Like

@jeffladiray Thanks a lot, i managed to do it without a lookup.

2 Likes