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

**URL:** <https://community.forestadmin.com/t/how-to-create-relationship-in-node-js-using-3-collections-in-a-hierarchical-order/6497>\
**Category:** Help me!\
**Tags:** howtos\
**Created:** [July 14, 2023, 9:07am UTC](https://community.forestadmin.com/t/how-to-create-relationship-in-node-js-using-3-collections-in-a-hierarchical-order/6497 "2023-07-14T09:07:04Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![zakarias](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/zakarias/32/6313_2.png) [@zakarias](https://community.forestadmin.com/u/zakarias)\
**Post date:** [July 14, 2023, 9:07am UTC](https://community.forestadmin.com/t/how-to-create-relationship-in-node-js-using-3-collections-in-a-hierarchical-order/6497/1 "2023-07-14T09:07:04Z")

</div>

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:

```javascript
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

---

<div class="post-metadata">

**Author:** ![anon39940173](https://avatars.discourse-cdn.com/v4/letter/a/8c91f0/32.png) [@anon39940173](https://community.forestadmin.com/u/anon39940173)\
**Post date:** [July 17, 2023, 8:43am UTC](https://community.forestadmin.com/t/how-to-create-relationship-in-node-js-using-3-collections-in-a-hierarchical-order/6497/2 "2023-07-17T08:43:05Z")

</div>

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:

```javascript
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`](https://docs.forestadmin.com/developer-guide-agents-nodejs/agent-customization/fields/import-rename-delete#moving-fields) 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

---

<div class="post-metadata">

**Author:** ![zakarias](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/zakarias/32/6313_2.png) [@zakarias](https://community.forestadmin.com/u/zakarias)\
**Post date:** [July 19, 2023, 10:26am UTC](https://community.forestadmin.com/t/how-to-create-relationship-in-node-js-using-3-collections-in-a-hierarchical-order/6497/3 "2023-07-19T10:26:26Z")

</div>

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_.

---

<div class="post-metadata">

**Author:** ![anon39940173](https://avatars.discourse-cdn.com/v4/letter/a/8c91f0/32.png) [@anon39940173](https://community.forestadmin.com/u/anon39940173)\
**Post date:** [July 20, 2023, 10:53am UTC](https://community.forestadmin.com/t/how-to-create-relationship-in-node-js-using-3-collections-in-a-hierarchical-order/6497/4 "2023-07-20T10:53:44Z")

</div>

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.

```javascript
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…

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

---

<div class="post-metadata">

**Author:** ![zakarias](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/zakarias/32/6313_2.png) [@zakarias](https://community.forestadmin.com/u/zakarias)\
**Post date:** [July 21, 2023, 9:21am UTC](https://community.forestadmin.com/t/how-to-create-relationship-in-node-js-using-3-collections-in-a-hierarchical-order/6497/5 "2023-07-21T09:21:55Z")

</div>

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.

```javascript
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`?

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/forest/original/2X/1/17cf3138fd161e64f83c1ca9cc4fbee35d9b5eb7.png)

## 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:

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

```

#### CustomerStampCardLogs:

```javascript
{
	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.

---

<div class="post-metadata">

**Author:** ![Arnaud\_Moncel](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/arnaud_moncel/32/24_2.png) [@Arnaud\_Moncel](https://community.forestadmin.com/u/Arnaud_Moncel)\
**Post date:** [July 26, 2023, 2:22pm UTC](https://community.forestadmin.com/t/how-to-create-relationship-in-node-js-using-3-collections-in-a-hierarchical-order/6497/6 "2023-07-26T14:22:05Z")

</div>

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:

```javascript
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:

```javascript
.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 🙏

---

<div class="post-metadata">

**Author:** ![zakarias](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/zakarias/32/6313_2.png) [@zakarias](https://community.forestadmin.com/u/zakarias)\
**Post date:** [July 31, 2023, 8:52am UTC](https://community.forestadmin.com/t/how-to-create-relationship-in-node-js-using-3-collections-in-a-hierarchical-order/6497/7 "2023-07-31T08:52:58Z")

</div>

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?

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

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

```

---

<div class="post-metadata">

**Author:** ![Arnaud\_Moncel](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/arnaud_moncel/32/24_2.png) [@Arnaud\_Moncel](https://community.forestadmin.com/u/Arnaud_Moncel)\
**Post date:** [July 31, 2023, 9:40am UTC](https://community.forestadmin.com/t/how-to-create-relationship-in-node-js-using-3-collections-in-a-hierarchical-order/6497/8 "2023-07-31T09:40:39Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![zakarias](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/zakarias/32/6313_2.png) [@zakarias](https://community.forestadmin.com/u/zakarias)\
**Post date:** [July 31, 2023, 11:48am UTC](https://community.forestadmin.com/t/how-to-create-relationship-in-node-js-using-3-collections-in-a-hierarchical-order/6497/9 "2023-07-31T11:48:01Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Arnaud\_Moncel](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/arnaud_moncel/32/24_2.png) [@Arnaud\_Moncel](https://community.forestadmin.com/u/Arnaud_Moncel)\
**Post date:** [July 31, 2023, 2:40pm UTC](https://community.forestadmin.com/t/how-to-create-relationship-in-node-js-using-3-collections-in-a-hierarchical-order/6497/10 "2023-07-31T14:40:57Z")

</div>

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](https://docs.forestadmin.com/user-guide/workspaces/building-a-workspace), you can build like below a workspace to display what you want.

 ![image](https://europe1.discourse-cdn.com/flex013/uploads/forest/original/2X/7/7323b8bcc7e97b30f5bedb1854d43ce436147787.png)

---

<div class="post-metadata">

**Author:** ![zakarias](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/zakarias/32/6313_2.png) [@zakarias](https://community.forestadmin.com/u/zakarias)\
**Post date:** [August 1, 2023, 12:41pm UTC](https://community.forestadmin.com/t/how-to-create-relationship-in-node-js-using-3-collections-in-a-hierarchical-order/6497/11 "2023-08-01T12:41:27Z")

</div>

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!