Include filter generating multiple queries instead of one

[nivruth-naka]

Describe the bug

https://github.com/loopbackio/loopback-next/discussions/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.

[yhrchan]

I did a quick test and got jsonb column to work with loopback-sequelize

For the model, you have to define it using "object" like so:

  @property({
    type: 'object',
    postgresql: {
      dataType: 'jsonb',
    },
  })
  test?: object;

For the filter, the dot notation could be used where "test" is the jsonb column:

{
  "where": {
  "and":[
    {"test.name": { "ilike": "%an%" }},
    {"test.address.city": { "ilike": "%no%" }}
   ]
  }
}

[nivruth-naka]

Thanks @yhrchan , great catch! I'll try this out shortly from my end to see if this helps. However, one thing to keep in mind is this nested format you showed above is different from the include syntax used in Juggler/Sequelize for relational object querying and may impact the underlying querying process. But if we are able to use jsonb with this nested syntax and internally have sequelize generate just one join query then it will help us. cc : @shubhamp-sf

[shubhamp-sf]

@nivruth-naka The reason it works with default juggler without specifying property as object is that they to do type-casting hack (see the ::TEXT in your query logs). Which stringifies the jsonb data and perform regular ilike match as It was with text. Which in my opinion isn't utilising jsonb capabilities and might lead to bad performance when the jsonb column contains heavily nested data.

PS: Thanks so much @yhrchan for testing this out.

[yhrchan]

@shubhamp-sf do you know how to enable SQL logging outputs from loopback-sequelize?

[shubhamp-sf]

@yhrchan loopback:sequelize:queries is used for that purpose.

It's mentioned in the debug string ref here.

[yhrchan]

@shubhamp-sf thanks got it to work

@nivruth-naka

{
  "where": {
    "code": {
            "ilike": "%33%"
          }
  },
  "include": [
    {
      "relation": "trans",
      "scope": {
        "where": {
          "test.name": {
            "ilike": "%o%"
          }
        }
      },
      "required": true
    }
  ]
}

[pookdeveloper]

Same problem here, and it is not only the way of querying but also that the relationships of the includes do not take them into account in the main table. Exmaple: I want to obtain the products including the category where the category name is 'CLOTHING'

From what I see, it launches 2 queries when it should be one, and it shows me both products with category and without category.