Error 500 too many tables

[shalmaxb]

Hello,
in a custom entity I get this error:

[2020-11-17 10:19:25] Espo.ERROR: (HY000) SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join; GET /Vermietung/5faaef914956a8304; line: 72, file: G:\laragon\www\Werkverzeichnis\application\Espo\OR M\SqlExecutor.php [] []
[2020-11-17 10:19:26] Espo.ERROR: (HY000) SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join; GET /Vermietung/5faaef914956a8304/tasks; line: 72, file: G:\laragon\www\Werkverzeichnis\application\Espo\OR M\SqlExecutor.php [] []
[2020-11-17 10:19:26] Espo.ERROR: (HY000) SQLSTATE[HY000]: General error: 1116 Too many tables; MySQL can only use 61 tables in a join; GET /Vermietung/5faaef914956a8304/dokumenteVermietung; line: 72, file: G:\laragon\www\Werkverzeichnis\application\Espo\OR M\SqlExecutor.php [] []

The entity is BasePlus and has quite a lot of fields (145) but not so many relationships (9). As I guess from the log this comes from the entity itself, documents and tasks.
Is there any way to solve this? I do not need necessarily tasks in this entity. Would it help to deactivate tasks and would that help?

[shalmaxb]

Meanwhile I tried to get a solution for this and found out that it might be because I use some one-to-one relationships. I do this, because I did not succeed with a multiple link relationship (like n:n or 1:n) to get data from one entity to another in a way, that I could use the values of the single fields, that are linked by that relationship. There is always only the ID available.
In fact I need more than one field from this other entity, because I have to do calculations and templates in the second entity, which is - as far as I know - not possible with multiple linked fields in espoCRM.

If anybody has a proposal, how to get the single field`s content from one entity into another for the described purpose, I would be able to solve an issue, that accompanies me since I started to work with espoCRM.

[Maximus]

Hi,
Probably this might help https://forum.espocrm.com/forum/bug-...-use-61-tables.

[shalmaxb]

I have already seen this thread, but I did not understand how to relate that to my entity, as in that thread, they put the noJoin to all fields, that have attachment. I do not have such fields and appendig it to other fields, did not work.
So, with these hints I still am a bit lost. Can I put the noJoin to any foreign field?

[Maximus]

Hi,
Well, this issue is related to a DB limitation. It is not recommended to put noJoin as it might cause an issue.
There are 2 thoughts:
1. You can try to find a way of how to change the limitation value.
2. Create a new Entity for storing the most part of the fields (e.g. let's say your original entity called 'Account', so you need to create a new one like 'Account Data'). Then relate them with one-to-one relationship to in order to be able to fetch data.