Computed Many to Many Relations

Help me!
1

Feature(s) impacted

We are currently migrating our service to use the new agent. As part of this process, I am trying to determine how to handle computed many-to-many relationships effectively.

For instance, we have a relationship between accounts and groups that we want to display. This relationship is computed dynamically on every call and requires us to call our backend endpoint to retrieve the groups associated with an account.

Using addExternalRelation isn’t feasible because:

  • It doesn’t support pagination.
  • It doesn’t allow linking to the groups page where we have defined actions and additional functionalities.

A computed foreign key also won’t work since it would require creating an accounts_groups table and adding or deleting rows every time the relationship is loaded, which is obviously not a solution.

Additionally, computing groups is somewhat expensive, so we want to avoid performing this computation for all accounts on the first page. Instead, we’d prefer to compute groups only when viewing detailed pages. Is that possible?

Observed behavior

Currently, the provided mechanisms like addExternalRelation and computed foreign keys don’t fully address our needs:

  • addExternalRelation lacks pagination and doesn’t integrate with existing group-related pages.
  • Computed foreign keys would require manual management of an intermediary table (accounts_groups) to simulate the many-to-many relationship, which is both inefficient and counterintuitive in this context.

Expected behavior

We would like a solution that:

  1. Allows us to define a computed many-to-many relationship without requiring manual creation or management of an intermediary table.
  2. Supports pagination for the relationship results.
  3. Integrates seamlessly with the related entity’s page (e.g., the groups page), enabling actions and other functionalities.
  4. Enables dynamic computation of the relationship only when necessary, such as when viewing detailed pages, to avoid unnecessary performance overhead.

Here is how it worked with forest-express-sequelize

collection('accounts', {
  fields: [
    {
      field: 'Groups',
      type: ['String'],
      reference: 'groups.name'
    },
   ...
  ]
})

router.get(
  '/accounts/:accountId/relationships/Groups',
  async (req: Request, res: Response) => {
    const { accountId } = req.params
    const limit = limitFromReq(req)
    const offset = offsetFromReq(req)

    try {
      const { data } = await api.get(
        `${config.apiDashboardUrl}/accounts/${accountId}?relations=groups&fields=groups.id`
      )

      const groups = await Groups.findAll({
        where: {
          id: {
            [Op.in]: data.groups.map((m) => m.id)
          }
        },
        limit,
        offset
      })

      return serializeAndSend(groups, Groups, res, data.groups.length)
    } catch (error) {
      return catchError(error)
    }
  }
)

Thanks for your help

Failure Logs

Context

  • Project name: [Commutifi
  • Team name: Commutifi
  • Environment name: [e.g., Development/Production]
  • Agent technology: Nodejs
  • Agent (forest package) name & version: @forestadmin/agent: 1.53.1
  • Database type: PostgreSQL
  • Recent changes made on your end if any: Migrating to the new agent
3

Hello @arthur,

I’m glad to hear that you are migrating to the latest agent version.

We have an experimental plugin that does more or less to what you are describing:

You may want to check the code and adapt it to fit your specifc use case (with the api call). This should let you do what you need.

regarding this last point
4. Enables dynamic computation of the relationship only when necessary, such as when viewing detailed pages, to avoid unnecessary performance overhead
→ this is something that you will have to configure on the frontend/layout side (remove it from list views as needed).

I hope this helps :pray: