# External relation with reference on a collection

**URL:** <https://community.forestadmin.com/t/external-relation-with-reference-on-a-collection/5935>\
**Category:** Help me!\
**Created:** [February 1, 2023, 8:35am UTC](https://community.forestadmin.com/t/external-relation-with-reference-on-a-collection/5935 "2023-02-01T08:35:06Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Guillaume\_Robin](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/guillaume_robin/32/862_2.png) [@Guillaume\_Robin](https://community.forestadmin.com/u/Guillaume_Robin)\
**Post date:** [February 1, 2023, 8:35am UTC](https://community.forestadmin.com/t/external-relation-with-reference-on-a-collection/5935/1 "2023-02-01T08:35:07Z")

</div>

## Feature(s) impacted

Customising collection with external relations

## Observed behavior

I can provide any kind of fields in my external relation, but I didn’t find how to tell Forest Admin that a field is actually a reference on another collection fields (as the old way in previous versions of Forest Admin where I could say `reference: "collection_name.id"`.

## Expected behavior

Be able to link external data to collection through references

## Context

- Project name: Inarix
- Agent type & version: NodeJS agent v1.6.4

---

<div class="post-metadata">

**Author:** ![shohanr](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/shohanr/32/4999_2.png) [@shohanr](https://community.forestadmin.com/u/shohanr)\
**Post date:** [February 1, 2023, 9:56am UTC](https://community.forestadmin.com/t/external-relation-with-reference-on-a-collection/5935/2 "2023-02-01T09:56:12Z")

</div>

Hi @Guillaume_Robin ,

The relationships documentation for the new agent is [here](https://docs.forestadmin.com/developer-guide-agents-nodejs/agent-customization/relationships).  
Is it what you need ?

If yes, my curious question is, did you know that there were a different documentation for the Agent NodeJS ? We try to gather some feedbacks about that so do not hesitate to comment on that.

Best regards,

Shohan

---

<div class="post-metadata">

**Author:** ![Guillaume\_Robin](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/guillaume_robin/32/862_2.png) [@Guillaume\_Robin](https://community.forestadmin.com/u/Guillaume_Robin)\
**Post date:** [February 1, 2023, 1:15pm UTC](https://community.forestadmin.com/t/external-relation-with-reference-on-a-collection/5935/3 "2023-02-01T13:15:30Z")

</div>

Hi @shohanr,

> Is it what you need ?

Yes, that’s the documentation but it doesn’t answer to my need unfortunately. Let me explain a bit deeper my need. Here is my models relations:

Users (id) ↔ Groups relation table (userId, groupId) ↔ Groups (id) ↔ Scenarios relation table (groupId, scenarioId) ↔ Scenarios

I would like to have the related scenarios for a user, and the related users to a scenario. However, with Sequelize, I can’t make a many-many through relation in this configuration and that’s why I tried to use external relations from forest admin to retries the list of users/scenarios.

My question is, when providing the list of records as the results of the external relation, to link a field to the id of a collection. For example, I want to link the user ID in my results to the collection users so that I can click on it to open the user record.

I hope it’s clearer 😋

> did you know that there were a different documentation for the Agent NodeJS ?

I noticed it when looking at the agent repository. At the beginning it was not clear but I figured it out.

Best regards,

Guillaume

---

<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:** [February 1, 2023, 2:12pm UTC](https://community.forestadmin.com/t/external-relation-with-reference-on-a-collection/5935/4 "2023-02-01T14:12:22Z")

</div>

Hello @Guillaume_Robin

I’m sure there are multiple ways to achieve what you need.  
The first one that I can think of is the following:

- [create a datasource that exposes a readonly collection that maps users to scenarios](https://docs.forestadmin.com/developer-guide-agents-nodejs/data-sources/custom/structure)
- [create a virtual many to many relation between user and scenarios](https://docs.forestadmin.com/developer-guide-agents-nodejs/agent-customization/relationships/multiple-records#many-to-many-relations)

The documentation could be way better, we still need to work on it.

Here is some code to get you started:

In your agent customization

```javascript
agent
  .addDataSource(require('./virtual-datasource.ts'))
  .customizeCollection('users', collection => collection.addManyToMany(...))
  .customizeCollection('scenarios', collection => collection.addManyToMany(...))

```

In a `virtual-datasource.ts` file

```javascript
import {
  AggregateResult,
  BaseCollection,
  BaseDataSource,
  Caller,
  DataSource,
  DataSourceFactory,
  PaginatedFilter,
  Projection,
  RecordData,
} from '@forestadmin/datasource-toolkit';

class UserScenarios extends BaseCollection {
  constructor(dataSource: DataSource) {
    super('UserScenarios', dataSource);

    const definition = {
      isPrimaryKey: true,
      type: 'Column',
      columnType: 'Number',
      filterOperators: new Set(['In' as const]),
    } as const;

    this.addField('userId', definition);
    this.addField('scenarioId', definition);
  }

  async list(
    caller: Caller,
    filter: PaginatedFilter,
    projection: Projection,
  ): Promise<RecordData[]> {
    // Extract the userIds and scenarioIds from the filter
    // This is trivial, because we know the filter is at most two IN operators with one AND
    // (because that's the only operator that we support, according to the constructor)
    let userIds: number[] = null;
    let scenarioIds: number[] = null;

    // filter.conditionTree can be null, you'll need to handle the case
    filter.conditionTree.forEachLeaf(leaf => {
      if (leaf.field === 'userId') userIds = leaf.value as number[];
      if (leaf.field === 'scenarioId') scenarioIds = leaf.value as number[];
    });

    // query sequelize to get the virtual relation table
   // you'll need to handle the case when either userIds is null, scenarioIds is null, or both
    const records = await sequelize.query(
      `
      SELECT t1."userId", t2."scenarioId"
      FROM groups_relation_table AS t1
      INNER JOIN scenarios_relation_table as t2 ON t1."groupId" = t2."groupId"
      WHERE t1."userId" IN (:userIds) AND t2."scenarioId" IN (:scenarioIds)
    `,
      { replacements: { userIds, scenarioIds } },
    );

    return projection.apply(records);
  }

  create(): Promise<RecordData[]> {
    throw new Error('Not supported');
  }

  update(): Promise<void> {
    throw new Error('Not supported');
  }

  delete(): Promise<void> {
    throw new Error('Not supported');
  }

  aggregate(): Promise<AggregateResult[]> {
    throw new Error('Not supported');
  }
}

export default function createVirtualRelation(): DataSourceFactory {
  return async (): Promise<DataSource> => {
    const dataSource = new BaseDataSource();
    dataSource.addCollection(new UserScenarios(dataSource));

    return dataSource;
  };
}

```

---

<div class="post-metadata">

**Author:** ![Guillaume\_Robin](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/guillaume_robin/32/862_2.png) [@Guillaume\_Robin](https://community.forestadmin.com/u/Guillaume_Robin)\
**Post date:** [February 1, 2023, 2:27pm UTC](https://community.forestadmin.com/t/external-relation-with-reference-on-a-collection/5935/5 "2023-02-01T14:27:33Z")

</div>

Oh! Great! I didn’t think about a virtual datasource 😱 Thanks a lot!

---

<div class="post-metadata">

**Author:** ![Guillaume\_Robin](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/guillaume_robin/32/862_2.png) [@Guillaume\_Robin](https://community.forestadmin.com/u/Guillaume_Robin)\
**Post date:** [February 2, 2023, 8:55am UTC](https://community.forestadmin.com/t/external-relation-with-reference-on-a-collection/5935/7 "2023-02-02T08:55:21Z")

</div>

Hi @anon39940173,

I’ve been experimenting with your proposal but I encountered an issue when I use the `columnType: Uuid`. Here is my code:

```javascript
class UsersScenarios extends BaseCollection {
  constructor(dataSource: DataSource) {
    super('UsersScenarios', dataSource);

    const definition = {
      isPrimaryKey: true,
      type: 'Column',
      columnType: 'Number',
      filterOperators: new Set(['In' as const]),
    } as const;

    this.addField('userId', {
      ...definition,
      columnType: 'Uuid',
      filterOperators: new Set(['Equal' as const]),
    });
    this.addField('scenarioId', definition);
  }

  async list(
    caller: Caller,
    filter: PaginatedFilter,
    projection: Projection
  ): Promise<RecordData[]> {
    // Extract the userIds and scenarioIds from the filter
    // This is trivial, because we know the filter is at most two IN operators with one AND
    // (because that's the only operator that we support, according to the constructor)
    let userIds: number[] = [];
    let scenarioIds: number[] = [];

    // filter.conditionTree can be null, you'll need to handle the case
    filter.conditionTree?.forEachLeaf(leaf => {
      if (leaf.field === 'userId') userIds = leaf.value as number[];
      if (leaf.field === 'scenarioId') scenarioIds = leaf.value as number[];
    });

    let query = `
      SELECT t1."userId", t2."scenarioId"
      FROM users.user_group_membership AS t1
      INNER JOIN users.user_group_scenario as t2 ON t1."groupId" = t2."groupId"
    `;
    let options = {};

    if (userIds.length > 0 && scenarioIds.length > 0) {
      query += `WHERE t1."userId" IN (:userIds) AND t2."scenarioId" IN (:scenarioIds)`;
      options = {replacements: {userIds, scenarioIds}};
    } else if (userIds.length > 0 && !scenarioIds.length) {
      query += `WHERE t1."userId" IN (:userIds)`;
      options = {replacements: {userIds}};
    } else if (!userIds.length && scenarioIds.length > 0) {
      query += `WHERE t2."scenarioId" IN (:scenarioIds)`;
      options = {replacements: {scenarioIds}};
    }

    // query sequelize to get the virtual relation table
    // you'll need to handle the case when either userIds is null, scenarioIds is null, or both
    const [records] = await SequelizeController.instance.query(query, options);

    return projection.apply(records as RecordData[]);
  }

  create(): Promise<RecordData[]> {
    throw new Error('Not supported');
  }

  update(): Promise<void> {
    throw new Error('Not supported');
  }

  delete(): Promise<void> {
    throw new Error('Not supported');
  }

  aggregate(): Promise<AggregateResult[]> {
    throw new Error('Not supported');
  }
}

export default function createUsersScenarios(): DataSourceFactory {
  return async (): Promise<DataSource> => {
    const dataSource = new BaseDataSource();
    dataSource.addCollection(new UsersScenarios(dataSource));
    return dataSource;
  };
}

// -- In another file
collection.addManyToManyRelation('accessibleScenarios', 'Scenario', 'UsersScenarios', {
    originKey: 'userId',
    foreignKey: 'scenarioId',
  });

```

But I get the following error when I try to access the related data of User scenarios:

```javascript
ConditionTreeEquivalent.getReplacer(...) is not a function 

TypeError: ConditionTreeEquivalent.getReplacer(...) is not a function
    at Function.getEquivalentTree (/***/forest-admin-backend/node_modules/@forestadmin/datasource-toolkit/dist/src/interfaces/query/condition-tree/equivalence.js:12:84)
    at /***/forest-admin-backend/node_modules/@forestadmin/datasource-customizer/dist/decorators/operators-equivalence/collection.js:32:69
    at ConditionTreeLeaf.replaceLeafs (/***/forest-admin-backend/node_modules/@forestadmin/datasource-toolkit/dist/src/interfaces/query/condition-tree/nodes/leaf.js:52:32)
    at /***/forest-admin-backend/node_modules/@forestadmin/datasource-toolkit/dist/src/interfaces/query/condition-tree/nodes/branch.js:31:84
    at Array.map (<anonymous>)
    at ConditionTreeBranch.replaceLeafs (/***/forest-admin-backend/node_modules/@forestadmin/datasource-toolkit/dist/src/interfaces/query/condition-tree/nodes/branch.js:31:73)
    at OperatorsEquivalenceCollectionDecorator.refineFilter (/***/forest-admin-backend/node_modules/@forestadmin/datasource-customizer/dist/decorators/operators-equivalence/collection.js:30:50)
    at OperatorsEquivalenceCollectionDecorator.list (/***/forest-admin-backend/node_modules/@forestadmin/datasource-customizer/dist/decorators/collection-decorator.js:38:42)
    at SearchCollectionDecorator.list (/***/forest-admin-backend/node_modules/@forestadmin/datasource-customizer/dist/decorators/collection-decorator.js:39:37)
    at processTicksAndRejections (node:internal/process/task_queues:96:5)

```

Do you have any idea of what I could miss? I tried to look at the source code of the agent, but I couldn’t get any clue of what’s going on.

Best regards,

Guillaume

---

<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:** [February 2, 2023, 10:36am UTC](https://community.forestadmin.com/t/external-relation-with-reference-on-a-collection/5935/8 "2023-02-02T10:36:49Z")

</div>

The issue comes from the fact that your virtual db does not supports the ‘Present’ operation which is needed for relations

The error message is terrible!  
I’m opening a ticket on our side to solve that.

Can you perform the following upgrade?

```javascript
// on constructor
// I noticed you used "Equal". It should be in
      filterOperators: new Set(['In' as const, 'Present' as const]),

```

```javascript
// on list
    filter.conditionTree?.forEachLeaf(({field, operator, value}) => {
      if (operator !== 'In') return;
      if (field === 'userId') userIds = value as number[];
      if (field === 'scenarioId') scenarioIds = value as number[];
    });

```

---

<div class="post-metadata">

**Author:** ![Guillaume\_Robin](https://dub1.discourse-cdn.com/flex013/user_avatar/community.forestadmin.com/guillaume_robin/32/862_2.png) [@Guillaume\_Robin](https://community.forestadmin.com/u/Guillaume_Robin)\
**Post date:** [February 2, 2023, 11:52am UTC](https://community.forestadmin.com/t/external-relation-with-reference-on-a-collection/5935/9 "2023-02-02T11:52:58Z")

</div>

Thanks a lot! That was it. It’s working as expected now 🤩