Can we make a relation between two tables that are in different databases

[jacques_liao]

Hello,

The goal is to set up a Related Data in one of the tables.
Is it possible to make a link between two tables that are in two different databases?
If not, is there any other solution to make the link between these two tables?

Thank you in advance.

[jacques_liao]

I tried to create a relationship between the beneficiary table and the refundRequest table where the beneficiary table has multiple refundRequests.

refundRequest file in the forest folder

beneficiary file in the forest folder

And when I go to the dashboard I get this

but in the logs I don’t see any error

So I thought that since the two tables are in different databases, the following error should occur.
Is there any other way to get around this?

Thanks in advance.

[Alban_Bertolini]

Hello,
We have a new JS/TS agent which allows you to do this in one line.

For your problem, do you have any error on the frontend ?
You should use a smart has many relationship not a belongs to.

[jacques_liao]

Hello @Alban_Bertolini
So I could try to make a hasMany relationship but I have the following error

I think I have this error because my two tables are not in the same database, one is in a database named Gateway and the other NP

And if not, concerning the new JS agent, which file should I put them in? forest, model, route or app.js file?

Thanks in advance

[Alban_Bertolini]

Hello
Could you send your code please ? You should make a first request to get your beneficiary and a second to get all the refundRequests related to the beneficiary. You can’t make one request because the collections are not in the same database. I think it should work but in any case you will not be able to write from the relation.
Also if you want to migrate to agentV2, you should use sequelize datasource and migrate all your models, smart actions, smart fields etc. by recoding them.

[jacques_liao]

Here is the code found in our routes folder /routes/main/beneficiaries

router.get('/beneficiaries/:beneficiary_id/relationships/refundRequests', (req, res, next) => {
  const beneficiaryId = req.params.beneficiary_id;
  const limit = parseInt(req.query.page.size, 10) || 20;
  const offset = (parseInt(req.query.page.number, 10) - 1) * limit;
  const include = [{
    model: beneficiaries,
    as: 'beneficiaries',
    where: { fkBeneficiaryId: beneficiaryId },
  }];

  // find the refundRequests for the requested page and page size
  const findAll = refundRequests.findAll({
    include,
    offset,
    limit,
  });

  // count all refundRequests for pagination
  const count = refundRequests.count({ include });

  // resolve the two promises and serialize the response
  const serializer = new RecordSerializer(refundRequests);
  Promise.all([findAll, count])
    .then(([refundRequestsFound, refundRequestsCount]) =>
      serializer.serialize(refundRequestsFound, { count: refundRequestsCount }))
    .then((recordsSerialized) => res.send(recordsSerialized))
    .catch(next);
});

the other piece of code in /forest/main/beneficiary

    {
      field: 'refundRequests',
      type: ['String'],
      reference: 'refundRequests.fkBeneficiaryId'
    }

[Alban_Bertolini]

Could you add a break point or a “console.log” to be sure which forest calls this end point please ?

[jacques_liao]

I put this console in my code

and I can find it in my server logs

[Alban_Bertolini]

Instead to do an “include”, could you use a where condition because the “include” will do a join between “beneficiaries” and “refundRequests”.

refundRequests.findAll({
  where: { fkBeneficiaryId: beneficiaryId },
});

[jacques_liao]

Yes, that was the solution !!!