Adding elements to a junction table that has additional fields

[Houssam]

Feature(s) impacted

• Generation of the models using the schema:update command
• Adding elements to a junction table that has additional fields

Observed behavior

The technical issue/limitation is the following:

Many-to-many relationships (with a junction table) are not detected properly, and as a result the two sides of the relationship get a HasMany with the junction table instead of a BelongsToMany with the other side of the relationship.

This only occurs if the junction table has additional fields, while the documentation specifies:

We detect Many-to-Many relationships when we detect a simple junction table. We are able to detect a junction table when it contains 2 foreign keys. It can optionally contain additional fields like a primary key and technical timestamps.

It is not clear from the documentation if this is restricted to "primary key"and “timestamps” fields, and I don’t see any reason for not having additional fields in the relationship.

The real impact from having the HasMany instead of the BelongsToMany relationship is the following:

When creating new entries in the junction table collection, we either have to specify both the related elements and their ids, “Member” and “Member id” / “Proposal” and “Proposal id”, (when keeping the default generated models):

Or if we manually remove the “memberId” and “proposalId” from the generated model, we get an "Id’ field, automatically added to the schema while not being in the model, that blocks creation as there is no id field on the db (and that seems to result from the HasMany relations):

Expected behavior

Only having the “Member” and “Proposal” fields on the UI when adding an element, without the “Member id” and “Proposal id”, and also not having that “Id” field that doesn’t correspond to anything.

This would be either by generating the BelongsToMany relations instead of HasMany, if that makes sense or in some other way if it doesn’t make sense to do so.

Context

• Project name: Collective.work
• Team name: Admin
• Environment name: Production or Preprod (both have the case where we have to specify both the element and its id in two different fields)
• Agent type & version: forest-express-sequelize 8.5.3 (Got the issue on 8.2.2, and updated to the latest in case it was a bug that was fixed)

[Houssam]

I actually see the limitation of having the BelongsToMany when the junction table has additional fields, as the form to add relationships doesn’t show the additional fields :

But it still doesn’t sound right to have to specify both an element and its id in two separate fields as shown in the screenshots of my post above!

[Arnaud_Moncel]

Hi @Houssam which version of forest --version do you use?

[Houssam]

Hello @Arnaud_Moncel, the version is the following forest-cli/2.4.1 darwin-x64 node-v16.14.2.

And to follow up on my previous messages, we came to the conclusion that the use of composite keys on junction tables that have additional fields is a bit broken.
As an example, using the default generated models, editing elements of the junction table collection breaks after adding an element in it. The issue that happens on the edit is that the generated query uses conditions such as WHERE firstId = "firstId|secondId" which fails. It is needed to refresh the browser tab for the edit to work again!

As a result, we went for a (hopefully temporary) workaround by adding an additional id field on those tables,.

[Arnaud_Moncel]

Hi @Houssam I tested and I reproduce your issue.
Unfortunately, improving this feature is not in the backlog for now.
I will push your request to our product board.

As a workaround, I suggest you to add an autogenerated id column on your junction table. I know it is useless for SQL point of view but it is necessary for now. Sorry about that.
Let me know if I can help about something else.

[Murat_AKSU]

any update? I think this is an important issue.

[Arnaud_Moncel]

Hi @Murat_AKSU can you open a new thread with your detailed issue please