Support for SQL JOIN (INNER JOIN)

[paeolo]

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: strongloop/loopback#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:
strongloop/loopback#683
loopbackio/loopback-datasource-juggler#730

or more recently,
#4995
#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 - #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.

[paeolo]

Afterthought, #4853 should give Loopback 4 a more complete ORM, with support for JOIN queries, hence solving this issue.

[Tronix117]

Should it be really closed ? It's a major issue not being able to restrict results using a filter on related models.

How can I list all my users, filtering them by a company name and role name ? It's a pretty standard need.

This issue was recurrent on Loopback 3, and has not been adressed in Loopback 4. There is the TypeORM, but from what I understood, it will not become the default one. So the issue still exists.

[achrinza]

@Tronix117

How can I list all my users, filtering them by a company name and role name

That's already possible with nested relations. It doesn't use native SQL JOIN but rather lb4 repository-level constraints (This design choice is to allow cross-database relations).

While the common database connectors do support foreign key constraints, the interface isn't well-defined, nor used JOINs, etc.

Hence, strong relations (and other things related to that) are being generally tracked by #2331.

[Tronix117]

@achrinza Alright, I will give you a better exemple.

Let's imagine :

User
---
email: string,
contactId: string

Contact
---
firstName: string,
lastName: string,
age: number,

I simplified the exemple, the discussion here is not about how the data are structured.

Now I want to show a paginated table of users with following columns : email, firstname, lastname, age.

Easy:

GET /users?filter={
  include: [
    { relation: 'contact' }
  ]
}

Now I want to filter this table, saying all people named Foo, what you are saying is

GET /users?filter={
  include: [
    {
      relation: 'contact',
      scope: {
        where: { firstName: 'Foo' }
     }
  ]
}

However this results in having ALL users displayed, but only the users named Foo will have a contact. The expected behavior would be to have only users with contact.firstName=Foo

Something like that would do it:

GET /users?filter={
  where: { 'contact.firtname': 'Foo' },
  include: [ { relation: 'contact' } ]
}

What we really want is the following SQL query SELECT * FROM users INNER JOIN contacts on user.contactId = contact.id WHERE contact.firstName = 'Foo'

---

It was not possible with Loopback 3, when I was using LB3, I rewrited a lot of the core logic to be able to do it. And it's still not possible with Loopback 4 (and the only workaround is to wait until the integration of TypeORM, or to make custom queries)

[paeolo]

@Tronix117 said:
Should it be really closed ? It's a major issue not being able to restrict results using a filter on related models.
The reason I closed the issue is two folds.

On the one hand, #683 was opened more than five years ago. To this day, we have no clues about whether the LB4 team is taking this issue seriously or not. Probably during these five years, they had to prioritize things and INNER JOIN support wasn't a priority, that's fine for me.

On the other hand, LB4 team made a great work at making it extensible, so it's very easy to support another ORM, for instance TypeORM or Sequelize. TypeORM not only supports INNER JOIN natively but is overall better than LB4 to me.

So while I was very disappointed to discover I couldn't make even an INNER JOIN with the default LB4 setup, I think TypeORM integration with LB4 solves this issue and many more: CLI, migrations, caching, generic queries. I tried recently to setup a starter project that integrates TypeORM into LB4, using #4853 as inspiration.

[paeolo]

Please let me know If you are not convinced and I will reopen this issue.

[Tronix117]

Thanks for your reply, I agree there is a lot of good stuff with LB4.
I also think we will switch to TypeOrm once it will be fully integrated.

However, I still think it should be something available out-of-the-box.

It’s not this hard to have a minimum viable by using INNER JOIN for belongsTo, to keep performance, it should be added only when it’s needed. Little bit harder to do this correctly with other relations since it involves subqueries and reduces performances.

That’s what we will do until TypeOrm integration is released.

So keeping this issue open is up to you, in my opinion it still makes sense.

[joel-izaguirre]

Is there any update on this? Is there a way to make a query with an inner join to related entity? something like:

SELECT * FROM users INNER JOIN contacts on user.contactId = contact.id WHERE contact.firstName = 'Foo'

[Tronix117]

There is a working connector with this option on our fork: https://github.com/Wikodit/loopback-connector-postgresql
Note that it is not compatible with querying along multiple connector (ex: postgre + mongo).

It supports all basic relations, as well as nested entities (without the need to include them), ex: /articles?filter[where][author.profile.firstName][ilike]=%foo% will produce a query similar to:

SELECT * FROM articles articles_0 
INNER JOIN author author_0 ON articles_0.authorId = author_0.id
INNER JOIN profile profile_0 ON author_0.profileId=profile_0.id
WHERE profile_0.firstName LIKE '%foo%'

We do not guarantee anything, but it fills all our needs, so we use that until Loopback provide a better solution.

Otherwise you will have to wait for the documentation and release of the TypeORM integration.

[joel-izaguirre]

In my case is for MongoDB any other suggestion?

[Tronix117]

In my case is for MongoDB any other suggestion?

Can’t you use nested document on mongo ? Otherwise there is no easy way to do it except going through aggregations and custom calls.

[mituso89]

There is a working connector with this option on our fork: https://github.com/Wikodit/loopback-connector-postgresql
Note that it is not compatible with querying along multiple connector (ex: postgre + mongo).

It supports all basic relations, as well as nested entities (without the need to include them), ex: /articles?filter[where][author.profile.firstName][ilike]=%foo% will produce a query similar to:

SELECT * FROM articles articles_0 
INNER JOIN author author_0 ON articles_0.authorId = author_0.id
INNER JOIN profile profile_0 ON author_0.profileId=profile_0.id
WHERE profile_0.firstName LIKE '%foo%'

We do not guarantee anything, but it fills all our needs, so we use that until Loopback provide a better solution.

Otherwise you will have to wait for the documentation and release of the TypeORM integration.

I followed https://github.com/Wikodit/loopback-connector-postgresql. add it needs a monkey patch and apply the patch but it dont working for me. any suggestion?

[samarpanB]

Inner joins are now possible via https://www.npmjs.com/package/@loopback/sequelize

[pookdeveloper]

There is an option to use typeorm instead of sequlize?