How to perform joins on non-primary columns and complex select statements in loopback 4?

[Rajamalli]

Describe the bug

Hi i am new to loopback and need to create an API with business logic that contains several complex queries. I cant seem to find a way to join two tables with non-primary columns. For example, select * from e_c left join e on e.ed=e_c.ed Table relations: e_c.ed fk to ed table on id e.ed fk to ed table on id

On the same note writing complex select statements are also needed. For example, select (case when completed_on is null then 1 else 0 end) flag from e or select sum(rating) from f_b

Kindly provide some method by which these can be achieved via loopback 4

Logs

No response

Additional information

No response

Reproduction

[Rajamalli]

need solution on a priority. Kindly help.

[shubhamp-sf]

While it wouldn't be the best solution but I could only think of running manual queries using the execute method on the repository to which you can pass your intended sql command.

Also loopback never run sql joins. You can read about it more here: https://github.com/loopbackio/loopback-next/issues/9345#issuecomment-1469870286

[Rajamalli]

While it wouldn't be the best solution but I could only think of running manual queries using the execute method on the repository to which you can pass your intended sql command.

Also loopback never run sql joins. You can read about it more here: #9345 (comment)

why does loopback not support joins?

[shubhamp-sf]

... and the reasoning might be that it provides a common set of interfaces for interacting with different data sources.

^ Quoting my comment on another issue. https://github.com/loopbackio/loopback-next/issues/9345#issuecomment-1469870286