How to create relationship in Node.js, using 3 collections in a hierarchical order?

[zakarias]

Question: In the Node.js backend environment, how do I create a relationship, using 3 collections referencing each other in a hierarchical order?

Setup

We have 3 collections, see the example docs below for the relationship:

merchant = { _id: 'merchant1' }
stampCardCampaign = { _id: 'campaign1', merchantId: 'merchant1' }
logs = { _id: 'log1', stampCardCampaignId: 'campaign1' }

We wish to create a relationship on the merchant to display all logs for all stamp cards. Documents in the logs collection do not have a reference to the merchant.

Is there a way to achieve this, without adding the merchantId to the log documents? (neither in the DB or with the addField method)?

We are migrating from the old forest-express-mongoose and are still learning the new Node.js way of structuring the code

Bonus question: How would I do it if the reference tree uses 4 or more collections? Before we could place our own logic in the routes, making it quite straightforward (not considering performance).

Context

• Project name: PayAtt Internal Admin Portal
• Team name: All teams
• Environment name: All environments
• Agent (old): forest-express-mongoose: ^8.7.9
• Agent (new): "@forestadmin/agent": "^1.13.4", & "@forestadmin/datasource-mongoose": "^1.4.2"
• Database type: MongoDB Atlas

[anon39940173]

To be honest I never actually encountered, nor tested this use-case (I’m the one who designed/implemented the API of agent v2 relations)

I would tend to say that following the logic of how I designed this, the obvious way to achieve what you want should be something like this:

agent.customizeCollection('merchant', collection => {
   collection.addOneToManyRelationship(
     'merchantLogs', // name of the relationship
     'logs', // name of the target table
     {
       // take the key from relation
       originKey: 'stampCardCampaign:merchantId'
     }
});

Before we go on further, can you check if you are getting any errors or if this is supported out-of-the-box?

If that does not works, we can have a look at the source code (on our side), and see why not.

In either case, avoid using addField at all costs for those use-cases: the importField method is much more concise and will automatically implement filtering, sorting, write passthrough, etc…

@zakarias

Bonus question: How would I do it if the reference tree uses 4 or more collections? Before we could place our own logic in the routes, making it quite straightforward (not considering performance).

That would depend on where the foreign keys are, so I’m not sure I can provide an easy answer to that

[zakarias]

Thank you for the response @anon39940173.

When I try to enter your suggestion, I get an error on the line: originKey: 'stampCardCampaignId:merchantId'. It only allows me to enter the direct fields of this collection (_id, stampCardCampaignId, etc).

Can you explain the colon : syntax? (if valid, seems to give an error). Would this perform a lookup under the hood?

Thanks for the heads up on addField vs importField.

[anon39940173]

hmm this is sad. I’m adding a feature request.

in the meantime, it looks you have no other option than import the fk, and use it.
This is more verbose than I would like.

agent
  .customizeCollection('logs', collection => {
    collection.importField('merchantId', { 
      path: 'stampCardCampaign:merchantId',
      readonly: true/false // depending on if you want the relation to be editable
    });
  });
  .customizeCollection('merchant', collection => {
    collection.addOneToManyRelationship('merchantLogs', 'logs', { originKey: 'merchantId' }
  });

Edit: error in the code sample

Edit2:

The colon syntax is to fetch columns from relations.
You can use it (almost) everywhere: on filters, smart field dependencies, etc…

book.addField('authorfullname', {
  columnType: 'String',
  dependencies: ['author:firstname', 'author:lastname'],
  getValues: records => records.map(r => `${r.author.firstname} ${r.author.lastname}`
}

[zakarias]

Bummer, thanks for putting up a feature request.

I’m trying to solve it using the suggestion you posted, but I can’t get it working. I’ll post the code below. Note that some names are slightly different than in previous examples, but I want to post the code now, without modification.

agent
	.customizeCollection('customer_stamp_card_logs', collection => {
		collection.importField('merchantId', {
			// Needed to add __manyToOne
			path: 'stampCardCampaignId__manyToOne:merchantId',
			readonly: false,
		});
	})
	.customizeCollection('Merchant', collection => {
		merchantsFields(collection);
		collection.addOneToManyRelation('customerStampCardLogs', 'customer_stamp_card_logs', { originKey: 'merchantId' });
	});

I don’t see the reference to the merchant on the customer_stamp_card_logs collection in the UI, do I need to do something more regarding importField?

Schemas:

I can navigate in the UI from the logs to the merchant, via the stamp card campaign (reference selected in the image above). So I know the references are OK. Furthermore, typescript gives no errors.

StampCardCampaign:

{
	merchantId: { type: mongoose.Schema.Types.ObjectId, required: true, ref: 'Merchant' },
	...
}

CustomerStampCardLogs:

{
	stampCardCampaignId: { type: mongoose.Schema.Types.ObjectId, required: true, ref: 'stamp_card_campaign'},
	...
}

I’m going on vacation for 1 week, so I might only be able to respond after that! Thanks for explaining the : syntax, it makes sense.

[Arnaud_Moncel]

Hi @zakarias I would like to sum up with you what I understand, and try to answer your question:

I don’t see the reference to the merchant on the customer_stamp_card_logs collection in the UI, do I need to do something more regarding importField ?

Here is your mongoose models:

connection.model(
  'Merchant',
  new mongoose.Schema({}),
);

connection.model(
  'StampCardCampaign',
  new mongoose.Schema({
    merchantId: { type: mongoose.Schema.Types.ObjectId, required: true, ref: 'Merchant' },
  }),
);

connection.model(
  'CustomerStampCardLogs',
  new mongoose.Schema({
    stampCardCampaignId: { type: mongoose.Schema.Types.ObjectId, required: true, ref: 'StampCardCampaign'},
  }),
);

Here is your agent code:

.addDataSource(createMongooseDataSource(connection))
.customizeCollection('CustomerStampCardLogs', collectionCustomizer => {
  collectionCustomizer
    .importField('merchantId', { path: 'stampCardCampaignId__manyToOne:merchantId' })
    // missing code to make the relation fully work
    .addManyToOneRelation('merchant', 'Merchant', { foreignKey: 'merchantId' });
})
.customizeCollection('Merchant', collectionCustomizer => {
  collectionCustomizer.addOneToManyRelation('customerStampCardLogs', 'CustomerStampCardLogs', { originKey: 'merchantId' });
})

If I understand correctly your issue, I thing you can have a look on the code who add merchant ManyToOneRelation on the CustomerStampCardLogs collection.

let me know if that help

[zakarias]

Hi @Arnaud_Moncel.

Thank you for the detailed example, now we got it to work!

Another related problem that we now encountered is that we want this relation to be “pre-filtered”. In other words, we don’t want to show all documents, but only a subset, adhering to a specific condition. How can we achieve this?

.addDataSource(createMongooseDataSource(connection))
.customizeCollection('CustomerStampCardLogs', collectionCustomizer => {
  collectionCustomizer
    .importField('merchantId', { path: 'stampCardCampaignId__manyToOne:merchantId' })

    // Can we add a filter here?
    .addManyToOneRelation('merchant', 'Merchant', { foreignKey: 'merchantId' });
})
...

[Arnaud_Moncel]

Can you explain me more your behavior please?
Do you want to always filter the table or just in the relation?
The specific condition are dynamic or not?

Let me know.

[zakarias]

We only want to filter on the relation, and the relation should always be filtered. The conditions are fixed.

As an example, the CustomerStampCardLogs collection in the code snippets from above, contains a field called logType, which can be, among others, rewardClaimed.

We wish to only show the documents with the logType=rewardClaimed in this specific relation.

[Arnaud_Moncel]

Unfortunately is not possible today by the code (easily). I push your feedback to our product board.

But you can create a workspace to reach your goal.
By reading our documentation, you can build like below a workspace to display what you want.

[zakarias]

We haven’t been using workspaces yet, as it makes sense for our admins to look at the data in the way it is structured in the “data” tab. However, it’s good to know we can achieve the behavior in workspaces.

Thanks for the help in this thread, it got us quite a bit further in our migration! Hopefully, the missing features will get added in due time.

Thank you!