Computed foreign keys

agent.customizeCollection('customers', collection => {
  // Create foreign key
  collection.addField('lastMessageId', {
    columnType: 'Number',
    dependencies: ['id'],
    getValues: async (customers, context) => {
      // We're using Forest's Query Interface (you can use an ORM or plain SQL)
      const messages = context.dataSource.getCollection('messages');
      const conditionTree = {
        field: 'customer_id',
        operator: 'In',
        value: customers.map(c => c.id),
      };

      const rows = await messages.aggregate(
        { conditionTree },
        { operation: 'Max', field: 'id', groups: [{ field: 'customer_id' }] },
      );

      return customers.map(record => {
        return rows.find(row => row.group.customer_id === record.id)?.value ?? null;
      });
    },
  });

  // Implement the 'In' operator.
  collection.replaceFieldOperator(
    'lastMessageId',
    'In',
    async (lastMessageIds, context) => {
      const records = await context.dataSource
        .getCollection('messages')
        .list(
          { conditionTree: { field: 'id', operator: 'In', value: lastMessageIds } },
          ['customer_id'],
        );

      return { field: 'id', operator: 'In', value: records.map(r => r.customer_id) };
    },
  );

  // Create relationships using the foreign key we just added.
  collection.addManyToOneRelation('lastMessage', 'messages', {
    foreignKey: 'lastMessageId',
  });
});

agent
  .customizeCollection('databaseUsers', createFilterableIdentityField)
  .customizeCollection('crmUsers', createFilterableIdentityField)
  .customizeCollection('databaseUsers', createRelationship)
  .customizeCollection('crmUsers', createInverseRelationship);

/**
 * Concatenate firstname, lastname and birthData to make a unique identifier
 * and ensure that the new field is filterable
 */
function createFilterableIdentityField(collection) {
  // Create foreign key on the collection from the database
  collection.addField('userIdentifier', {
    columnType: 'String',
    dependencies: ['firstName', 'lastName', 'birthDate'],
    getValues: user => user.map(u => `${u.firstName}/${u.lastName}/${u.birthDate}`),
  });

  // Implement 'In' filtering operator (required)
  collection.replaceFieldOperator('userIdentifier', 'In', values => ({
    aggregator: 'Or',
    conditions: values.map(value => ({
      aggregator: 'And',
      conditions: [
        { field: 'firstName', operator: 'Equal', value: value.split('/')[0] },
        { field: 'lastName', operator: 'Equal', value: value.split('/')[1] },
        { field: 'birthDate', operator: 'Equal', value: value.split('/')[2] },
      ],
    })),
  }));
}

/** Create relationship between databaseUsers and crmUsers */
function createRelationship(databaseUsers) {
  databaseUsers.addOneToOneRelation('userFromCrm', 'crmUsers', {
    originKey: 'userIdentifier',
    originKeyTarget: 'userIdentifier',
  });
}

/** Create relationship between crmUsers and databaseUsers */
function createInverseRelationship(crmUsers) {
  crmUsers.addManyToOneRelation('userFromDatabase', 'databaseUsers', {
    foreignKey: 'userIdentifier',
    foreignKeyTarget: 'userIdentifier',
  });
}