How to make relation between two collections alternatives

Help me!
1

Hello FA team,

We are trying to make a relation between two collections, accounts and users. Every account has many users. We did that in a easier way through the code like this:

Accounts.hasMany(models.users, {
      foreignKey: 'account_id',
      targetKey: 'id',
});

Users.belongsTo(models.accounts, {
      foreignKey: 'account_id',
});

And this is work well on our dev environment, but when we run it on the production Accounts collection page is broken. The reason is that the Forest Admin join these two tables and make a relation between all Accounts and Users at one call. A query is large so FA could not get any response. In some envs it takes about 1 hour to accomplish the query. So this solution is not possible in this case.

Do we have any alternative solution? Maybe smart views could help?

Theoreticlly this procedure could help:

  1. Open a single Account.
  2. Make a query to get all users belongs to opened account.
  3. Present the response.

But we are not sure how to achieve this approach. Any suggestion?

Thank you,
Bojan

2

Hi @Bojan_Antonijevic,

Could you please share your agent version ?
Is this forest-express-sequelize ?

Is your issue happening in the listing or in the record details ?

3

Hi @vince,

Using Chrome Version 85.0.4183.83.
Yes it’s “forest-express-sequelize”: “^5.2.0”.

It’s happening when we open a collection to list all Accounts. In that moment query is started. If that query is triggered when we open a single record detail it shouldn’t be a problem I guess.

And because of that we could not even see the list of Accounts.

4

Could you send the query send to your server to retrieve your accounts ?

5

Could not. It will be a security issue to expose the structure of the tables publicly.

But is it possible to add some section which will contain the query like:
SELECT * FROM users WHERE account_id={id}
and present it somehow.

Screenshot 2020-09-24 at 15.09.14
Screenshot 2020-09-24 at 15.09.141638Ă—1244 148 KB

6

Hmm okey.
Why is Users not showing in your Related Data I thought that Account had many Users no ?
It should already be the case. But I think I missing a important information :thinking:

7

Yep, accounts has many users. And we make a relation as on the question of the topic through the code. And code is working perfectly fine on development where we have few hundreds users.

But when we open Accounts page to see all the records. Just accounts, not users. To see users we need to open single account and we could not reach to that step.

Reason is that on the Accounts page Forest Admin make a query to the database to join this 2 tables where we have a lots of data and take to much time to be executed. As result page is not rendered completely.

8

Okey then can you send me the request send to the server when trying to retrieve the accounts ?

9

We could see this example when we try to count users.
The user count on the search bar does not appear.
Pagination is not working.
The same is happen when we make a relation between accounts and users(Accounts page is broken)

EDIT by @rap2h: screenshot removed with the agreement of @Bojan_Antonijevic

10

an what is the response of the count query :thinking:

11

No response. Just failed because takes more than 1min.

Screenshot 2020-09-25 at 10.41.24
Screenshot 2020-09-25 at 10.41.241770Ă—80 18.8 KB

But anyway the question is could we somehow implement smart view or scope in detail of a single record? In that way, we could specify queries per account and avoid troubles.

12

If you are on postgres, try to override your route on /users/count:

  try {
    const noFilterOrSearch = !request.query.search && !request.query.filter;
    if (noFilterOrSearch) {
      const result = await models.sequelize
        .query("SELECT reltuples AS count FROM pg_class WHERE relname = 'users';");
      const { count } = result[0][0];
      response.send({ count });
    } else {
      next();
    }
  } catch (error) {
    next(error);
  }

Your count should be way faster like that. And should fix your pagination

13

You could override routes, but what I’m worried about is that you should not have any issue by default :thinking:. So I think there is an issue that we need to fix before trying to getting around it

14

Hey @vince, can we schedule a meeting so I could share you a screen to check the issues?

15

Hi @Bojan_Antonijevic :wave:

Just a quick note: Assuming it works locally and not remotely due to timeout there may be a problem with the database size and/or the server configuration. Looking at one of your screenshots, it seems that the users’ table is big.

My best bet is that if you increase your remote server capacity and check what you can tune in your database configuration, it would work faster and better. Even if we would improve some queries, as soon as your database grows, you have to increase your server’s capacity. You may find some ways to optimize (see workaround by @vince) but you would have to rewrite some queries to fine-tune them.

:point_right: As a side note, I suggest you edit your screenshot to remove all personal information, such as emails, names, and your bearer token; I could do this for you if you prefer)

1 Like
16

Thanks @rap2h

Hey @Bojan_Antonijevic, I have reached out by email, let’s discuss this further to investigate on this!

1 Like
17

Thanks guys, we will continue with discussing on the meeting.

1 Like
18

@rap2h thank you on your note. I do not have an option to edit a message, just remove icon is present. Can you just remove a screenshot, and leave the message?

1 Like
19

@Bojan_Antonijevic I removed the screenshot and added an “edit” note: How to make relation between two collections alternatives

1 Like