[@loopback/sequelize] Duplicate column names in relation queries

[KalleV]

Describe the bug

Repository queries with relations that do not explicitly set all the keyTo / keyFrom / through properties will lead to broken SQL queries that contain duplicated title case column names mixed in with the expected camelcase columns.

Example:

SELECT bookid as bookId, reader.id as readerId, ReaderId …

Relates to: https://github.com/sequelize/sequelize/issues/9328 https://github.com/loopbackio/loopback-next/issues/9591 https://github.com/sourcefuse/loopback4-sequelize/issues/35

Logs

No response

Additional information

Workaround seems to be to go through all the Entity relations and explicitly set all the relation key names but that can be time-consuming and error-prone with a larger project. It seems like it might be possible to mitigate this at the loopback model to Sequelize relation layer.

Reproduction

With these changes, running the tests for the Sequelize extension will replicate the error: https://github.com/KalleV/loopback-next/commit/3c29d852b46f19f4805d556ae32aee49c79471f1

Turns out it's necessary to define additional "belongsTo" relations in other entities before this happens. The extra relation is added to the "Patient" entity in this case. With this set up, I am seeing the following happen:

• The default relation properties are set by Loopback (i.e. I can see the "keyFrom" is populated as todoListId) but the "keyTo" is undefined:
• This leads to undefined being passed as the foreign key to sequelize:
• And then that causes Sequelize to assign a Title Case property through it's own default relation column logic leading to a duplicate column name in the database query:

[shubhamp-sf]

I personally feel that when it comes to anything that gets included in query be it table name, column name, relations keys etc. We should explicitly set it rather than relying on LoopBack's defaults for the obvious surety reasons.

Although it would be very beneficial for new adopters to face as little code changes as possible for them to implement this in their project.

I already have so much in my bucket list so I'll pick this up in the first week of July, unless any one else from the community wants to work on this.

[samarpanB]

@KalleV I see some commits from you on your forked repo. Were you able to fix this issue ? If yes, would you mind to submit a PR in this repo too, so that community can benefit from it too ?