Loopback-next: Include related models: bug if foreign key is null

[eliaoucohen72]

Hello,
I am using loopback 4 for my server side. I built my models with the new inclusion functionality.
I have model A and model B, In model A, there is a foreign key of model B. When I make a get request, I get the information with the whole object of the foreign key. Everything works perfectly but ... my foreign key is not required. So, when I make a get request in case the foreign key is null, I get the following error:

DEFAULT is not allowed in this context on Connection.parseE ...

This problem only exists if in the database there are rows where the fk is null and another where the fk is not null.
For example:

id: 1,
name: "aaaa",
modelb_id: null

id: 2,
name: "bbb",
modelb_id: 1

I mean if in all the rows the fk is null, everything works perfectly.

Anyone knows a solution ???
Thank you

[dhmlau]

Transferred to loopback-next repo.

[dhmlau]

@deepakrkris , could you please take a look? Thanks.

[eliaoucohen72]

Someone has checked the bug?

[deepakrkris]

@eliaoucohen72 I am not able to recreate this issue with in-memory connector and mysql, which database are you using ?
Can you also give the exact URL that is failing and the complete stack trace.
Is your GET call trying to do a list or access a specific id ?
If possible please explain the issue in terms of one of the examples (for instance, todo example)

[eliaoucohen72]

which database are you using?

Postgres

Is your GET call trying to do a list or access a specific id?

Both

charge.model.ts

@model({
  name: "Charge",
  settings: {
    strict: false,
    foreignKeys: {
      currency_id: {
        name: "charge_currency_id",
        foreignKey: "currency_id",
        entityKey: "id",
        entity: "tenant_configuration"
      }
    }
  }
})
export class Charge extends Entity {
  @property({
    type: "number",
    id: true,
    generated: true
  })
  id: number;

  @property({
    type: "number",
    required: true,
    postgresql: {
      dataType: "decimal"
    }
  })
  amount: number;

  @belongsTo(
    () => TenantConfiguration,
    { keyFrom: "currency_id", name: "currency" },
    { index: true }
  )
  currency_id: number;

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

export interface ChargeRelations {
  currency?: TenantConfigurationWithRelations;
}

export type ChargeWithRelations = Charge & ChargeRelations;

tenant_configuration.model.ts

@model({
  name: "tenant_configuration",
  settings: {
    strict: false
  }
})
export class TenantConfiguration extends Entity {
  @property({
    type: "number",
    id: true,
    generated: true
  })
  id: number;

  @property({
    type: "string",
    required: true,
    index: true
  })
  key_name: string;

  @hasMany(() => Charge, { keyTo: "currency_id" })
  charges?: Charge[];

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

export interface TenantConfigurationRelations {
  charges?: ChargeWithRelations[];
}

export type TenantConfigurationWithRelations = TenantConfiguration &
  TenantConfigurationRelations;

code for my GET request

    const filterBuilder = new FilterBuilder<Charge>()
      .include({relation: 'currency'})
      .build();

    let filteredCharges = await this.chargeRepository.findAllActive(filterBuilder);

In this case that the FK is null, I get the error, but if in all the rows the FK exists, it works and I get all the currency object

[basavarajvs]

I faced a similar issue with POSTGRESQL. Based on the information available from stackoverflow https://stackoverflow.com/questions/54158349/how-to-use-default-in-postgres I updated all the rows whose value was null for the relation. The problem was n more seen. Not sure if in every case we can have a NON-NULL value in a relation field.. But this helped me.

[deepakrkris]

I used the following scripts in a local postgres instance and tried with the existing todo-list example as is. please note that the foreign key todoListId is null for one of the rows in the todo table. Still a GET call works fine and gets the list of todos

CREATE TABLE public.todolist
("id" int8 NOT NULL,
title varchar(25),
color varchar(25),
PRIMARY KEY ("id"))

CREATE TABLE public.todo (
    "id" int8 NOT NULL,
    title varchar(25),
    "desc" varchar(25),
    iscomplete bool,
    todoListId int8,
    PRIMARY KEY ("id")
);

ALTER TABLE public.todo ADD CONSTRAINT todoListId FOREIGN KEY (todoListId) REFERENCES todolist(id)

CREATE UNIQUE INDEX title_index ON todo (title);

INSERT INTO public.todolist (id, title, color) VALUES (1,  'green', 'green' );

INSERT INTO public.todo (id, title, "desc", todoListId, iscomplete) VALUES (1, 'green',  'basketball', null, true);

INSERT INTO public.todolist (id, title, color) VALUES (2,  'red', 'red' );

INSERT INTO public.todo (id, title, "desc", todoListId, iscomplete) VALUES (2, 'red',  'soccer', 2, true);

select * from public.todo

select * from public.todolist

[deepakrkris]

the request url http://localhost:3000/todos?filter[limit]=100, gets a list of all todos even though the FK todoListId is null.

[deepakrkris]

@eliaoucohen72 is the case you are dealing different than the one I have described above ? In that case I have a few questions:

1. usually a list function is supported out of the box and for including relations an InclusionResolver is added as in the todo-list example. In your example, what the charge repository and its method findAllActive() are trying to query ? is it a specific join condition ?
2. can you provide a simplified DDL of both the tables you are having issue with ?

[eliaoucohen72]

@eliaoucohen72 is the case you are dealing different than the one I have described above ? In that case I have a few questions:

1. usually a list function is supported out of the box and for including relations an InclusionResolver is added as in the todo-list example. In your example, what the charge repository and its method findAllActive() are trying to query ? is it a specific join condition ?
2. can you provide a simplified DDL of both the tables you are having issue with ?

I have recreated a new project with the TodoList example with a postgres database. I want to send you all the project that you can run it on your computer (just add your username and password in the db.datasource.config.json file, and do npm i and npm install loopback-connector-postgresql --save). Can you give me your email to send you?

[dougal83]

Can you give me your email to send you?

@eliaoucohen72 Just upload it to your github account as a public repo and post link here?

[eliaoucohen72]

Can you give me your email to send you?

@eliaoucohen72 Just upload it to your github account as a public repo and post link here?

https://github.com/eliaoucohen72/lb4_todolist_with_postgres

[deepakrkris]

@eliaoucohen72 I am able to recreate the error with the repo you provided. I will update this issue with the fix required.

[deepakrkris]

This issue occurs because of two parts:

1. the Belongs-To resolver in loopback-next tries to fetch objects even for foreign key marked as NULL
2. the Postgres connector tries to set DEFAULT value to null keys in where clause (only for id fields), which causes error. This check seems to be an unnecessary one, postgres does not seem to complain for NULL values placed in where clauses even for id fields.

[deepakrkris]

opened issue https://github.com/strongloop/loopback-next/issues/4372 for case 1 above.
opened issue https://github.com/strongloop/loopback-connector-postgresql/issues/407 for case 2 above.

[deepakrkris]

issue in description closed for post gres with strongloop/loopback-connector-postgresql#407 , following up for other connectors with https://github.com/strongloop/loopback-next/issues/4372