Support for SQL JOIN (INNER JOIN)

Hi,
First, I am a very enthusiastic user of Loopback 4 and use it for one of my personal project, namely https://github.com/DimitriTuaz/radio-didou.
While I use MongoDB for this project, I planned to use Loopback with a PostgreSQL database for another project.
However there is an issue that I find very disturbing, namely the impossibly to do a SQL Join with the built-in Loopback 4 ORM.

@bajtos explained very well the situation:

@pookdeveloper IIUC, the condition specified in include.scope.where is applied only to related models being fetched. This issue is asking for a different feature: filter the source models based on properties of the related model.

What does not work:

Books with title contain "Art" and belongs to Category "Programming"

What may work using include.scope.where:

Books with title containing "Art", include data of all related categories with the name "Programming".

I hope my example is making the difference clear.

See the associated issue here: https://github.com/strongloop/loopback/issues/683. In the following I will refer to the two cases described by @bajtos as case 1 and case 2 respectively.

To use a more formal terminology, and if I am not mistaken, while Loopback 4 supports a "LEFT OUTER JOIN" (case 2) it does not support an "INNER JOIN" (of which case 1 is an instance).

The thing is when you are using MongoDB it's not a problem at all, since MongoDB doesn't support generic JOIN anyway! But when you are using a SQL database it's just natural to do a join, or at least to be able to perform case 1.

Many issues are related to this:
https://github.com/strongloop/loopback/issues/683
https://github.com/strongloop/loopback-datasource-juggler/issues/730

or more recently,
https://github.com/strongloop/loopback-next/issues/4995
https://github.com/strongloop/loopback-next/issues/4299

Now while I am aware that I can do a LEFT OUTER JOIN and then filter the response in the controller, for instance, I think it's not a good practice since the database is designed to do it for you, so for me it's not a good solution.

I am pretty sure there is a workaround here (the one I have just cited for instance:p) but that's not my point too. My point is I find Loopback 4 a more than awesome framework to develop a REST API, with an incredible level of coherence and it's very frustrating to be blocked by not being able to perform case 1 within the framework.

To quote @Rayjax from one year ago:

This is completely insane.

I am not completely aware of the difficulty of the task, specially since you must design something in the Query Langage that won't work with every database, MongoDB for instance, but it doesn't seem that hard regarding the great work you've done with Loopback 4.

I am also aware you planned to support TypeORM - https://github.com/strongloop/loopback-next/issues/4853 - and while I think it is awesome, it's more another feature than solving this issue to me.

So I decided to open this issue cause after a lot of searching, I read all the topics on it and it's still not clear to me if at least you planned to support this in some near future.

Again, I still think Loopback 4 is one of my best DX and if no one is available to do this, I am available to do it, at least for case 1.

Best regards,

Paul.

Comienza revisando el comportamiento integrado del ORM de LoopBack 4 en torno a include.scope.where y los issues relacionados enlazados en el informe, especialmente strongloop/loopback#683 y loopback-next#4995. Define cómo debe funcionar el caso 1 para los orígenes de datos SQL, preservando al mismo tiempo el comportamiento para MongoDB; se considera completado cuando los modelos de origen puedan filtrarse por las propiedades de los modelos relacionados mediante el framework, en lugar de aplicar el filtrado en el controlador.