Multiple Many-to-one Relationships between the same two Tables breaks

[Debugnium]

Hosting

• Self
  • Method: docker compose
  • App Version: 1.1.21
  • Database: MS SQL Server

Describe the bug

Having multiple many-to-one relationships from Table A to Table B seems to break relationships:

Expected behavior

"Employees" relationships to load an Employee when viewing "Customers" data.

Additional context

In my case, a legacy database has 3 relations between customers and employees representing different roles.

The SQL seen in the XEvent Profiler uses an or in the left join, and in the select statement, it includes all of the [Employees] columns three times... left join [Employees] on [Customers].[Employee ID] = [Employees].[Employee ID] or [Customers].[OrgEmpID] = [Employees].[Employee ID] or [Customers].[Service ID] = [Employees].[Employee ID]

This results in 3 rows with the same "Employees" data repeated 3 times in each row, rather than a single row with data for each employee.

Removing two of the three relationships fixes the issue, but then two relationships are missing.

[martkaczmarek]

Also, having many-to-many relationship on tables A-B-C prevents us from creating one-to-many relationship on tables A-B (even though it's the same thing, it is sometimes useful in forms building)

[melohagan]

Possibly related issue in MySQL.

Message from Discord:

if I create/define an relationship with Table A to Table B, then do it Table A to Table C, the the A<->B goes away. IF I create a relationship Table B to Table A, then Table C to Table A, it works fine,

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity.

[melohagan]

Closing as multiple Many->Many relationships with the same tables will no longer be supported: https://github.com/Budibase/budibase/pull/9421

This is because as well as the break, it would be substantially detrimental on performance. Better to use Custom SQL queries if you need to perform a lot of joins.