Adding relationship between two tables from two different databases

Help me!
1

Feature(s) impacted

I’m trying to add a relationship in a table called reward which is in the database called rewards. The relationship is of the belongsTo. I’ve tried to do something like the following in the reward model:

Reward.belongsTo(models.merchant, {
      foreignKey: {
        name: 'merchantIdKey',
        field: 'merchant_id',
      },
      as: 'merchant',
      tableName: 'emma.merchant',
    });

Also added the following to the schema for the reward model:
{ "field": "merchant", "type": "Number", "defaultValue": null, "enums": null, "integration": null, "isFilterable": true, "isPrimaryKey": false, "isReadOnly": false, "isRequired": false, "isSortable": true, "isVirtual": false, "reference": "merchant.id", "inverseOf": null, "relationship": "BelongsTo", "validations": [] }

But I get the following error:

[forest] 🌳🌳🌳  Unexpected error: Table 'rewards.merchant' doesn't exist
{
  "name": "SequelizeDatabaseError",
  "parent": {
    "code": "ER_NO_SUCH_TABLE",
    "errno": 1146,
    "sqlState": "42S02",
    "sqlMessage": "Table 'rewards.merchant' doesn't exist",
...

Which is accurate since the merchant table is in the emma DB rather than rewards. Any suggestions would be greatly appreciated.

Thanks,
Sahil

Observed behavior

Can’t add relationship from a table in another DB.

Expected behavior

Able to add a relationship from a table in another DB.

Context

  • Project name: Emma
  • Team name: Rewards
  • Environment name: Development | sahilmemon
  • Agent technology: Node.js
  • Agent (forest package) name & version: forest-cli@5.0.9, forest-express-sequelize@9.0.0
  • Database type: MySQL
  • Recent changes made on your end if any: Updated a model and schema in the codebase.
2

Hello @sahilm1806,
You can’t add a relation between two databases with sequelize. Sequelize is an ORM, it knows only its database context.

Fortunately, forestadmin has developed a solution for this case: the smart relationship. You can follow this guide. :pray:

Best regard

1 Like
3

Thanks @Alban_Bertolini,

That fixed it for me! :tada:

4

Still having a bit of an issue when trying to set the merchant field, i.e. update it from the UI. I get this is my logs:

PUT /forest/reward/1/relationships/merchant?timezone=Europe%2FLondon 404 177 - 5.565 ms

My code for the smart field:

collection('reward', {
  actions: [],
  fields: [
    {
      field: 'merchant',
      type: 'Number',
      reference: 'merchant.id',
      get: async (reward) => {
        return await models.merchant.findOne({
          where: {
            rewardId: reward.id,
          },
        });
      },
      set: async (reward, merchant) => {
        const rewardBeforeUpdate = await reward.findOne({
          where: { id: reward.id },
        });

        reward.merchant = merchant;
        return reward;
      },
    },
  ],
  segments: [],
});

Would appreciate any suggestions.

5

Hello,
You can’t add a set property when adding a smart relationship.
You should add a new PUT route. Either you can follow this guide, or you can follow this thread on our community support.. All steps are detailed on the thread. In your case, you can only implement the PUT route.

Let me know if it works,
Alban

6

In that community thread, it says to update I should add this route, which I implemented for my case:

router.put(
  '/reward/:rewardId/relationships/merchant',
  async (request, response, next) => {
    try {
      // data.id contains the id of the selected merchant in the UI
      if (request.body.data.id) {
        // Get all the record needed and update
        const reward = await models.reward.findByPk(request.params.rewardId);
        const merchant = await models.merchant.findByPk(request.body.data.id);
        reward.merchant = merchant.id;
        await reward.save();
        response.send(reward);
      }
    } catch (err) {
      console.log(err);
      next(err);
    }
  },
);

But data.id is null for me. I’ve added the logs below:

[forest] 🌳🌳🌳  Unexpected error: Cannot read properties of null (reading 'id')
{
  "stack": "TypeError: Cannot read properties of null (reading 'id')\n    at <anonymous> (/Users/sahilmemon/Emma/backend/nodejs/packages/forest-admin-backend/routes/rewards.js:238:29)\n    at Layer.handle (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/layer.js:95:5)\n    at next (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/route.js:144:13)\n    at Route.dispatch (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/route.js:114:3)\n    at Layer.handle (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/layer.js:95:5)\n    at <anonymous> (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:284:15)\n    at param (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:365:14)\n    at param (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:376:14)\n    at Function.process_params (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:421:3)\n    at next (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:280:10)\n    at Function.handle (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:175:3)\n    at router (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:47:12)\n    at Layer.handle (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/layer.js:95:5)\n    at trim_prefix (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:328:13)\n    at <anonymous> (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:286:9)\n    at Function.process_params (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:346:12)\n    at next (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:280:10)\n    at <anonymous> (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:646:15)\n    at next (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:265:14)\n    at Function.handle (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:175:3)\n    at router (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:47:12)\n    at Layer.handle (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/layer.js:95:5)\n    at trim_prefix (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:328:13)\n    at <anonymous> (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:286:9)\n    at Function.process_params (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:346:12)\n    at next (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:280:10)\n    at <anonymous> (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:646:15)\n    at next (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:265:14)\n    at Function.handle (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:175:3)\n    at router (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:47:12)\n    at Layer.handle (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/layer.js:95:5)\n    at trim_prefix (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:328:13)\n    at <anonymous> (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:286:9)\n    at Function.process_params (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:346:12)\n    at next (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:280:10)\n    at <anonymous> (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:646:15)\n    at next (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:265:14)\n    at Function.handle (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:175:3)\n    at router (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/index.js:47:12)\n    at Layer.handle (/Users/sahilmemon/Emma/backend/nodejs/node_modules/express/lib/router/layer.js:95:5)"
}
PUT /forest/reward/10/relationships/merchant?timezone=Europe%2FLondon 500 103 - 8.890 ms
PUT /forest/reward/10/relationships/partner?timezone=Europe%2FLondon 204 - - 16.270 ms

Upon logging request.body.data, I see that it is null. I assume the id was changes to somewhere/something else, since that community thread is from 2020.

Also logged the entire request but nowhere do I see the primary key from the merchant collection.

7

Hello,
In your case you are in the belongs to relation.
You can retrieve your merchant from the foreign key of the reward object.

Example:

const reward = await models.reward.findByPk(request.params.rewardId);
const merchant = await models.merchant.findByPk(reward.merchantId);

Note: MerchantId is for the example, it’s probably an other name in your model. Also, you can optimise you request by doing a jointure.

Have a nice week,
Alban

1 Like