Loopback `include` generates multiple queries instead of one join query

Question

I have two models Main & Trans (one to one association)

import {Entity, hasOne, model, property} from '@loopback/repository';
import {Trans, TransWithRelations} from '.';

@model({
  settings: {idInjection: false, postgresql: {schema: 'public', table: 'main'}}
})
export class Main extends Entity {
  @property({
    type: 'number',
    required: true,
    hidden: true,
    scale: 0,
    id: 1,
    postgresql: {columnName: 'id', dataType: 'bigint', dataLength: null, dataPrecision: null, dataScale: 0, nullable: 'NO'},
  })
  id: number;

  @property({
    type: 'number',
    required: true,
    scale: 0,
    postgresql: {columnName: 'code', dataType: 'integer', dataLength: null, dataPrecision: null, dataScale: 0, nullable: 'NO'},
  })
  code: number;

  @property({
    type: 'string',
    postgresql: {columnName: 'grade', dataType: 'text', dataLength: null, dataPrecision: null, dataScale: null, nullable: 'YES'},
  })
  grade?: string;

  @property({
    type: 'string',
    postgresql: {columnName: 'ebmethod', dataType: 'text', dataLength: null, dataPrecision: null, dataScale: null, nullable: 'YES'},
  })
  ebMethod?: string;

  @property({
    type: 'number',
    scale: 0,
    postgresql: {columnName: 'cdnper', dataType: 'integer', dataLength: null, dataPrecision: null, dataScale: 0, nullable: 'YES'},
  })
  cdnPer?: number;

  @property({
    type: 'number',
    scale: 0,
    postgresql: {columnName: 'usper', dataType: 'integer', dataLength: null, dataPrecision: null, dataScale: 0, nullable: 'YES'},
  })
  usPer?: number;

  @property({
    type: 'boolean',
    postgresql: {columnName: 'mandatory', dataType: 'boolean', dataLength: null, dataPrecision: null, dataScale: null, nullable: 'YES'},
  })
  mandatory?: boolean;

  @property({
    type: 'date',
    postgresql: {columnName: 'effectivedate', dataType: 'date', dataLength: null, dataPrecision: null, dataScale: null, nullable: 'YES'},
  })
  effectiveDate?: string;

  @property({
    type: 'date',
    postgresql: {columnName: 'expirydate', dataType: 'date', dataLength: null, dataPrecision: null, dataScale: null, nullable: 'YES'},
  })
  expiryDate?: string;

  @hasOne(() => Trans, {keyTo: 'mainId'})
  trans?: Trans;

  // Define well-known properties here

  // Indexer property to allow additional data
  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  [prop: string]: any;

  constructor(data?: Partial<Main>) {
    super(data);
  }
}

export interface MainRelations {
  // describe navigational properties here
  trans?: TransWithRelations;
}

export type MainWithRelations = Main & MainRelations;

import {belongsTo, Entity, model, property} from '@loopback/repository';
import {Main} from './main.model';

@model({
  settings: {idInjection: false, postgresql: {schema: 'public', table: 'trans'}, hiddenProperties: ['mainId']}
})
export class Trans extends Entity {
  @property({
    type: 'number',
    required: true,
    hidden: true,
    scale: 0,
    id: 1,
    postgresql: {columnName: 'id', dataType: 'bigint', dataLength: null, dataPrecision: null, dataScale: 0, nullable: 'NO'},
  })
  id: number;

  @property({
    type: 'string',
    postgresql: {columnName: 'segment', dataType: 'jsonb', dataLength: null, dataPrecision: null, dataScale: null, nullable: 'YES'},
  })
  segment?: string;

  @property({
    type: 'string',
    postgresql: {columnName: 'subsegment', dataType: 'jsonb', dataLength: null, dataPrecision: null, dataScale: null, nullable: 'YES'},
  })
  subsegment?: string;

  @property({
    type: 'string',
    postgresql: {columnName: 'cdnratebasis', dataType: 'jsonb', dataLength: null, dataPrecision: null, dataScale: null, nullable: 'YES'},
  })
  cdnRateBasis?: string;

  @property({
    type: 'string',
    postgresql: {columnName: 'usratebasis', dataType: 'jsonb', dataLength: null, dataPrecision: null, dataScale: null, nullable: 'YES'},
  })
  usRateBasis?: string;

  @belongsTo(() => Main)
  mainId?: number;

  // Define well-known properties here

  // Indexer property to allow additional data
  // eslint-disable-next-line @typescript-eslint/no-explicit-any
  [prop: string]: any;

  constructor(data?: Partial<Trans>) {
    super(data);
  }
}

export interface TransRelations {
  // describe navigational properties here
}

export type TransWithRelations = Trans & TransRelations;

I want to filter based on multiple columns in both tables and have a sample filter such as

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

For this I am seeing that Loopback is generating multiple queries instead of one. The full log can be seen [here][1] and the same Git repo has other code such as repositories, controllers, etc. Has anyone been able to workaround this?

score 1 · Answer 1 · answered Mar 15 '23 at 11:54

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 it doesn't yet.

The Solution

To address this problem, we at Sourceloop 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.

Loopback `include` generates multiple queries instead of one join query

1 Answers1

The Cause

The Solution