List entity's grandchildren (hasMany>hasMany collection)

[antoine]

I have Events, which have multiple Deals, and each Deal has multiple Shotguns (tickets).
How can I list all the Shotguns of an Event, without having to go through each Deal?

Feature(s) impacted

Smart fields / smart relationships

Observed behavior

I was able to implement an Event smart field on the Shotgun entity:

    {
      field: "Event",
      type: "Number",
      // isFilterable: true, // TODO
      description: "The shotgun's deal's event",
      reference: "events.id",
      get: async (shotgun) => {
        const [event] = await events.findAll({
          include: [
            {
              model: deals,
              as: "deals",
              where: {
                id: shotgun.dealId,
              },
              include: [
                {
                  model: shotguns,
                  as: "shotguns",
                  where: {
                    id: shotgun.id,
                  },
                },
              ],
            },
          ],
        });

        // OR:
        const deal = await DealDataloaders.byId.load(shotgun.dealId);
        const event = await EventDataloaders.byId.load(deal.eventId);

        return event;
      },
    },

Although I’m not sure if it’s better to use include or dataloaders (performance seems similar).

Expected behavior

I’m failing to do the opposite listing: list an Event’s Shotguns (without having to manually write a smart collection).

Context

• Project name: Shotgun admin
• Team name: Shotgun
• Environment name: Staging & production
• Agent (forest package) name & version: forest-express-sequelize 9.2.8
• Database type: PG 14
• Recent changes made on your end if any: N/A

[anon39940173]

Using forest-express-*

I can confirm that this is a non-trivial problem.

Smart relationships and smart collections are basically the same under the hood: you still need to implement all the routes to list / get / update the records.

I can point you to the smart relationship and route override documentation

• Create a Smart relationship - Developer guide
• Override a route - Developer guide (to see RecordGetter in action)

As you are only jumping over one level, and those both are has many relationships, your best bet (what I would try first anyways) would be to abuse our filtering system to inject a filter for your foreign key in the relationship routes so that you can use RecordsGetter, and not need to reimplement everything (filtering etc…)

But it is difficult for me to predict any issues that you may have without trying it on a sample project.

Something in the lines of

// In the collection file
collection('event', {
  fields: [
    {
      field: 'shotguns',
      type: ['String'],
      reference: 'shotguns.id',
    },
  ],
});

// router file
router.get('/event/:event_id/relationships/shotguns', (request, response, next) => {
  const { query, user } = request;

  // import those from the forest package
  const getter = new RecordsGetter(shotguns, user, query);
  const counter = new RecordsCounter(shotguns, user, query);

  // Hack the filter to add the event_id condition
  const injected = { field: 'deal:event_id', operator: 'equal', value: request.params.event_id };
  const filter = JSON.stringify(request.query.filter);
  if (filter?.aggregator)
    filter.conditions.push(injected);
  else
    filter = injected;
  
  request.query.filter = JSON.stringify(filter);

  // Fetch the data
  const [records, count] = await Promise.all([getter.getAll(), counter.count()]);

  // Respond to frontend
  response.send(await getter.serialize(records, { count }));
});

// you'll also need to add all the other routes, but those are more straightforward
router.get(...)
router.post(...)
router.delete(...)

---

Using the new @forestadmin/agent

This much shorter and cleaner sample should do the job

• To multiple records - Developer guide
• Computed foreign keys - Developer guide

createAgent()
  // Create a foreign key on the shotgun collection that jumps over deals
  .customizeCollection('shotguns', shotguns => {
    shotguns.importField('eventIdThroughDeals', { path: 'deal:event:id', readonly: true });
  })

  // Use the foreign key to create a relationship
  .customizeCollection('events', events => {
    events.addOneToManyRelation('shotgunsThroughDeal', 'shotguns', {
      originKey: 'eventIdThroughDeals',
    });
  });

[antoine]

Thank you @anon39940173, it works!

// forest/events.js
collection("events", {
  fields: [
    {
      field: "shotguns",
      type: ["Number"],
      reference: "shotguns.id",
      // isFilterable: true, // TODO
    }
  ],
});


// routes/events.js
router.get(
  "/events/:event_id/relationships/shotguns",
  permissionMiddlewareCreator.list(),
  async (request, response, next) => {
    const { params, query, user } = request;

    const injected = {
      field: "deal:event_id",
      operator: "equal",
      value: params.event_id,
    };
    let filters;
    if (query.filters) {
      filters = JSON.parse(query.filters);
      if (filters.aggregator) {
        filters.conditions.push(injected);
      } else {
        filters = { aggregator: "and", conditions: [filters, injected] };
      }
    } else {
      filters = injected;
    }
    query.filters = JSON.stringify(filters);

    const counter = new RecordsCounter(shotguns, user, query);
    const getter = new RecordsGetter(shotguns, user, query);

    const [records, count] = await Promise.all([
      getter.getAll(),
      counter.count(),
    ]);

    response.send(await getter.serialize(records, { count }));
  },
);

(not sure why I need to implement POST/DELETE, updating a Shotgun seems to work without)

But it does not resolve the filtering issue in the sense that my coworker is trying to create a workspace dashboard using this:

[anon39940173]

Could you show me the request that is generated?

[antoine]

Hi @anon39940173, I have another need that I could not resolve:
In the other collections, there is a default “export” action, but not in this smart collection, do you know how I can add it? I don’t even know if I can add actions to it anyway, maybe it’s not possible