Loopback-next: BelongsTo does not work well with large IDs

[frbuceta]

Steps to reproduce

I have changed the names of the properties in a BelongsTo relationship, the id is now longer (example: '34006051'), and loopback has stopped giving me the relationship, now it doesn't include it anymore

The field by which the BelongsTo is made has id but still does not return the object.
Will the id be too big? I also have a HasMany and it works.

Before

    {
        "id": "432",
        ...
        "deliveryOrderId": 3400606,
        "deliveryOrder": {
            "id": 3400606,
            ...
        },
        "orders": [
            {
                "id": 3400606,
                ...
            },
            {
                "id": 3400606,
                ...
            }
        ]
    }

Now

    {
        "id": "432",
        ...
        "deliveryOrderId": "34006061",
        ...
        "orders": [
            {
                "id": 34006060,
                ...
            },
            {
                "id": 34006061,
                ...
            }
        ]
    }

Additional information

node -e 'console.log(process.platform, process.arch, process.versions.node)'
darwin x64 13.8.0

npm ls --prod --depth 0 | grep loopback

├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
├── @loopback/[email protected]
└── [email protected]
npm ERR! extraneous: [email protected] /Users/fran/Projects/....api/node_modules/loopback-connector-mssql/node_modules/readable-stream

[achrinza]

I noticed that the orders.id values are truncated by 1 character (e.g. 3400606 vs 34006060).

Is this also unexpected behaviour?

[frbuceta]

I noticed that the orders.id values are truncated by 1 character (e.g. 3400606 vs 34006060).

Is this also unexpected behaviour?

No, this is like this in the database. It is right
As in this database (to which we do not have control) the IDs are duplicated, we have added a number more

[frbuceta]

Can someone help me a little with this? @strongloop/loopback-next
At least if someone knows where the fault can come from to analyze it

[derdeka]

@frbuceta Can you please provide an example repo which reproduces the issue. (see https://loopback.io/doc/en/contrib/Reporting-issues.html)

[agnes512]

@frbuceta I just checked it on my end with MySQL and Postgres (not MsSQL tho) DBs. I was able to traverse data via belongsTo relation.

      const thor = await customerRepo.create({id: 87654321, name: 'Thor'});
      const odin = await customerRepo.create({id: 87654322, name: 'Odin'});
      const thorOrder = await orderRepo.create({
        id: 12345678,
        description: "Thor's Mjolnir",
        customerId: thor.id,
      });
      const odinOrder = await orderRepo.create({
        id: 22345678,
        description: "LoopBack",
        customerId: odin.id,
      });

^ the case works fine on both MySQL and Postgres.

And for your case I notice that you have string type as your fk in your belongsTo relation:

    {
        "id": "432",
        ...
        "deliveryOrderId": "34006061", // string type, while others are number
        ...
        "orders": [
            {
                "id": 34006060,
                ...
            },
            {
                "id": 34006061,
                ...
            }
        ]
    }

I am not sure if it's typo or you defined it as string type. Could you try number type and see if it works? If not, I will investigate more with MsSQL. Thanks!

[frbuceta]

@frbuceta I just checked it on my end with MySQL and Postgres (not MsSQL tho) DBs. I was able to traverse data via belongsTo relation.

      const thor = await customerRepo.create({id: 87654321, name: 'Thor'});
      const odin = await customerRepo.create({id: 87654322, name: 'Odin'});
      const thorOrder = await orderRepo.create({
        id: 12345678,
        description: "Thor's Mjolnir",
        customerId: thor.id,
      });
      const odinOrder = await orderRepo.create({
        id: 22345678,
        description: "LoopBack",
        customerId: odin.id,
      });

^ the case works fine on both MySQL and Postgres.

And for your case I notice that you have string type as your fk in your belongsTo relation:

    {
        "id": "432",
        ...
        "deliveryOrderId": "34006061", // string type, while others are number
        ...
        "orders": [
            {
                "id": 34006060,
                ...
            },
            {
                "id": 34006061,
                ...
            }
        ]
    }

I am not sure if it's typo or you defined it as string type. Could you try number type and see if it works? If not, I will investigate more with MsSQL. Thanks!

Right, one is string and other is number

Source:

  @belongsTo(() => Order, undefined, {
    name: 'pedidoEnCurso',
    required: true,
  })
  deliveryOrderId: number;

Target:

  @property({
    type: 'number',
    id: true,
    generated: true,
  })
  id?: number;

What could be happening here? interesting...

[frbuceta]

In the database they are of type bigint

[dougal83]

In the database they are of type bigint

Is the DB expecting to exceed 2 billion records?! A lot of space being wasted via indexing if not.

[frbuceta]

In the database they are of type bigint

Is the DB expecting to exceed 2 billion records?! A lot of space being wasted via indexing if not.

I'm right with you, but it's not in my power to change this setting

[emonddr]

@agnes512 , can you take a quick look? thx.

[frbuceta]

I just got to change the fields to integer and it works fine. With bigint it doesn't work
@emonddr @agnes512

[odykyi]

this works for me

@model({
  settings: {
    foreignKeys: {
      fk_todo_todoListId: {
        name: 'fk_todo_todoListId',
        entity: 'TodoList',
        entityKey: 'id',
        foreignKey: 'todolistid',
      },
    },
  },
})
export class Todo extends Entity {
  //etc.
}

https://loopback.io/doc/en/lb4/todo-list-tutorial-sqldb.html#specify-the-foreign-key-constraints-in-todo-model

[stale[bot]]

This issue has been marked stale because it has not seen activity within six months. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository. This issue will be closed within 30 days of being stale.