Include filter generating multiple queries instead of one

[nivruth-naka]

Describe the bug

#9344

Logs

https://github.com/nivruth-naka/lb-include/blob/main/lb-include.log

Additional information

No response

Reproduction

https://github.com/nivruth-naka/lb-include

[shubhamp-sf]

The Cause

Loopback never run SQL JOIN Queries internally. It uses inclusion resolvers which is responsible to include related models and is called after your source table's data is returned. Resulting in two different calls when you use include in your filter object.

This behaviour is driven by juggler (the ORM/ODM loopback uses), and the reasoning might be that it provides a common set of interfaces for interacting with different data sources.

You might say that "We're just running SQL database, it should support JOINs in this case". Well yes, but the bad news is this is currently not the case.

The Solution

To address this problem, we at ARC have developed an extension for LoopBack that utilizes Sequelize ORM, which supports join queries for SQL databases. This extension has recently been added as the official Loopback framework extension and can be found on the npm package page:

https://www.npmjs.com/package/@loopback/sequelize

Although the package is labeled as experimental, it has been tested and is continually being improved based on feedback. Additionally, two of the limitations mentioned in the README are expected to be resolved real soon as their PR is opened for review.

Please feel free to use the extension and provide feedback or report any issues on Github as the I'm actively addressing them.

[nivruth-naka]

@shubhamp-sf ,

getting this error in spite of adding primaryKey: true. any idea?

Error: A column called 'id' was added to the attributes of 'main' but not marked with 'primaryKey: true'

[nivruth-naka]

Is this the correct way to add?

[shubhamp-sf]

Yes, it is. Please use id: true along with it.

[nivruth-naka]

thanks @shubhamp-sf . now seeing

Error: SequelizeConnectionRefusedError: connect ECONNREFUSED 127.0.0.1:5432

how does datasource setup connection? is it different from default juggler? I don't have 127.0.0.1:5432 in my code, so unsure where it's picking this from

[shubhamp-sf]

It's not different from juggler, but this port is default for postgresql so it might be the case that you're missing out those config in the datasource.

Similar to Below is what works for both juggler and sequelize.

const config = {
  name: 'db',
  connector: 'postgresql',
  host: 'localhost',
  port: 5003,
  user: 'postgres',
  password: 'super-secret',
  database: 'postgres',
};

see if you are missing anything?

[nivruth-naka]

We are actually reading this from a .env file, this process works fine if I revert Sequelize changes back to Juggler but somehow Sequelize datasource is looking for something else.

[shubhamp-sf]

Currently url strings are not respected in the sequelize extension. I've noted down the issue and it'll be fixed in the upcoming releases.
Meanwhile you can use the specific keys to specify settings like host, port, etc. (they can come from .env)

[nivruth-naka]

@shubhamp-sf,
in that case it looks like it's defaulting to postgres local credentials, would be good to throw an error right away.
if possible, could you please point me to a working example for the connection using host, port, etc?

[shubhamp-sf]

Sure here you go: https://github.com/shubhamp-sf/loopback4-sequelize-transaction-example

[nivruth-naka]

@shubhamp-sf ,
seeing
status code 500. Error: SequelizeDatabaseError: operator does not exist: jsonb ~~* unknown

does this work for postgres jsonb columns as well? if yes, please suggest how to fix the filter

{
  "where": {
    "and": [
      {
        "effectiveDate": {
          "lte": "2023-03-01"
        }
      },
      {
        "expiryDate": {
          "gte": "2023-03-01"
        }
      }
    ]
  },
  "order": "code asc",
  "include": [
    {
      "relation": "trans",
      "scope": {
        "where": {
          "and": [
            {
              "segment": {
                "ilike": "%Ware%"
              }
            }
          ]
        }
      },
      "required": true
    }
  ]
}

[nivruth-naka]

My use case is basically to include some strings and do a case insensitive search within a jsonb column. The above filter (without required = true) works in Loopback / Juggler but is too slow as there are multiple queries.

[shubhamp-sf]

does this work for postgres jsonb columns as well?

This seems to a postgres specific type, and current release of @loopback/sequelize doesn't address this type. Will raise a PR for this in a day or two and let you know.

Thanks for sharing your use-case.

[nivruth-naka]

Thanks @shubhamp-sf ! yes, it is postgres specific but a widely used column type and the current default implementation of Loopback (with Juggler) supports this type of querying.