lb3 - Relations on uuid #4287

fasenderos · 2019-11-29T14:08:47Z

Steps to reproduce

When the foreignKey of the relations point to ID everything works as expected, while if I try to point the foreignKey to UUID, the UUID is parsed as integer (NaN) and the relation not work.

To reproduce create 2 models:

Customer

{
  "name": "customer",
  "base": "PersistedModel",
  "idInjection": true,
  "options": {
    "validateUpsert": true
  },
  "properties": {
    "id": {
      "type": "number"
    },
    "uuid": {
      "type": "string",
      "defaultFn": "uuidv4"
    },
    "name": {
      "type": "string"
    }
  },
  "validations": [],
  "relations": {
    "addresses": {
      "model": "address",
      "type": "hasMany",
      "foreignKey": "customerId"
    }
  },
  "acls": [],
  "methods": {}
}

Address

{
  "name": "address",
  "base": "PersistedModel",
  "idInjection": true,
  "options": {
    "validateUpsert": true
  },
  "properties": {
    "id": {
      "type": "number"
    },
    "uuid": {
      "type": "string",
      "defaultFn": "uuidv4"
    },
    "customerId": {
      "type": "number",
      "mysql": {
        "columnName": "customer_id"
      }
    },
    "customerUuid": {
      "type": "string",
      "mysql": {
        "columnName": "customer_uuid"
      }
    },
    "street": {
      "type": "string"
    }
  },
  "validations": [],
  "relations": {
    "customer": {
      "model": "customer",
      "type": "belongsTo",
      "foreignKey": "customerId"
    }
  },
  "acls": [],
  "methods": {}
}

Get customer with address related by ID:

const customer = await app.models.Customer.findById(1, {  include: { relation: 'addresses' } });
console.log(customer);

Now in Address replace "foreignKey": "customerId" with "foreignKey": "customerUuid" and you get customerUuid as NaN:

If you do the same thing for Customer model by replacing the "foreignKey": "customerId" with "foreignKey": "customerUuid", you get an empty addresses list

Expected Behavior

The ability to use UUID on to relate model

Additional information

Node: v10.14.1
Npm: v6.4.1
Database: 10.1.30-MariaDB

lb --version
5.0.3 ([email protected] [email protected])

"dependencies": {
    "compression": "^1.0.3",
    "cors": "^2.5.2",
    "helmet": "^3.10.0",
    "loopback": "^3.22.0",
    "loopback-boot": "^2.6.5",
    "loopback-component-explorer": "^6.2.0",
    "loopback-connector-mysql": "^5.4.2",
    "serve-favicon": "^2.0.1",
    "strong-error-handler": "^3.0.0"
  }

The text was updated successfully, but these errors were encountered:

fasenderos · 2019-11-29T14:47:31Z

Maybe here is same issue on LB4 loopbackio/loopback-next#3602 that seems to be solved

bajtos · 2019-11-29T14:51:33Z

Thank you for reporting the issue. Since it's related to LoopBack 3.x, I am going to move it to strongloop/loopback repository.

bajtos · 2019-11-29T14:55:37Z

Maybe here is same issue on LB4 loopbackio/loopback-next#3602 that seems to be solved

This looks like a reasonable assumption to me. Can you try to apply loopbackio/loopback-datasource-juggler#1783 to the juggler module installed in your dependencies and then add useDefaultIdType: false to uuid property definition?

fasenderos · 2019-11-29T15:37:48Z

Thank you for your reply.
I replace in loopback-datasource-juggler/lib/datasource.js on line 711
if (idProp && idProp.generated && this.connector.getDefaultIdType)
with
if (idProp && idProp.generated && idProp.useDefaultIdType !== false && this.connector.getDefaultIdType)

Then edited Customer and Address models as below:

### Customer and Address
"uuid": {
   "type": "string",
   "useDefaultIdType": false,
   "defaultFn": "uuidv4"
}

### Address
"customerUuid": {
    "type": "string",
    "useDefaultIdType": false,
    "mysql": {
      "columnName": "customer_uuid"
    }
}

But I get same results, uuid NaN.

bajtos · 2019-11-29T16:17:13Z

Hmm, then the problem may be somewhere else.

fasenderos · 2019-11-29T16:48:20Z

The juggler always look at the id prop, so adding "id": true to uuid property make the relation work again:

"uuid": {
   "type": "string",
   "id": true,
   "useDefaultIdType": false,
   "defaultFn": "uuidv4"
}

UPDATE: works even without useDefaultIdType

"uuid": {
   "type": "string",
   "id": true,
   "defaultFn": "uuidv4"
}

fasenderos · 2019-12-02T16:15:39Z

Although the model relations now seem to work fine, most of the methods don't.

For ex Customer.create() returns an object without the ID prop but with the auto incremented id in the UUID prop, like this:

"uuid": 100, -> The auto incremented ID
"name": "John Doe"

While in the db the entry is:

"id": 100
"uuid": "281d25ba-1685-463f-812a-0f1c2b77bbd9",
"name": "John Doe"

agnes512 · 2020-01-02T17:40:39Z

@fasenderos I think LB3 takes the id property as the source key by default. That's why when set the uuid property to id: true the relation works. I am not sure if lb3 can customize the source key. Can you try to set the primary key of relations as:

Customer

... customer mode def
  "properties": {
    "id": {
      "id": true,   // model primary key is still id 
      "generated": true,
      "type": "number"
    },
    "uuid": {
      "type": "string",
      "defaultFn": "uuidv4"
    },
   }..
.....
  "relations": {
    "addresses": {
      "model": "address",
      "type": "hasMany",
      // edit: should be "customerUuid"
      "foreignKey": "customerId",
      "primaryKey": "uuid" . // customized primary key
    }
  },

Address

... address mode def
  "relations": {
    "addresses": {
      "model": "customer",
      "type": "belongsTo",
      // edit: should be "customerUuid"
      "foreignKey": "customerId",
      "primaryKey": "uuid" .   // customized primary key
    }
  },

I am also curious about the result of Customer.create() . I have no clue why it returns the wrong uuid and ignores id. Do you mind posting your Customer model definition here? thanks.

fasenderos · 2020-01-03T09:53:12Z

I am also curious about the result of Customer.create() . I have no clue why it returns the wrong uuid and ignores id. Do you mind posting your Customer model definition here? thanks.

@agnes512 first I post my model def without your suggestions

Customer

{
  "name": "customer",
  "base": "PersistedModel",
  "idInjection": false,
  "options": {
    "validateUpsert": true
  },
  "properties": {
    "id": {
      "type": "number"
    },
    "uuid": {
      "id": true,
      "type": "string",
      "defaultFn": "uuidv4"
    },
    "name": {
      "type": "string"
    }
  },
  "validations": [],
  "relations": {
    "addresses": {
      "model": "address",
      "type": "hasMany",
      "foreignKey": "customerUuid"
    }
  },
  "acls": [],
  "methods": {}
}

Address

{
  "name": "address",
  "base": "PersistedModel",
  "idInjection": false,
  "options": {
    "validateUpsert": true
  },
  "properties": {
    "id": {
      "type": "number"      
    },
    "uuid": {
      "id": true,
      "type": "string",      
      "defaultFn": "uuidv4"
    },
    "customerId": {
      "type": "number",
      "mysql": {
        "columnName": "customer_id"
      }
    },
    "customerUuid": {
      "type": "string",      
      "mysql": {
        "columnName": "customer_uuid"
      }
    },
    "street": {
      "type": "string"
    }
  },
  "validations": [],
  "relations": {
    "customer": {
      "model": "customer",
      "type": "belongsTo",
      "foreignKey": "customerUuid"
    }
  },
  "acls": [],
  "methods": {}
}

Then I create a boot script /boot/test.js

'use strict';

module.exports = async function (app) {
    let Customer = app.models.Customer;
    let Address = app.models.Address;

    // Create Customer
    let createdCustomer = await Customer.create({ name: 'John Doe' });
    console.log("Customer.create() ", createdCustomer);    

    // Retrieve customer by ID to get the UUID of the customer
    let customer = await Customer.findOne({
        where: { id: createdCustomer.uuid } // <------ UUID instead of ID
    });
    console.log("\nCustomer.findOne()", customer);

    // Create address with customer reference
    let createdAddress = await Address.create({customerUuid: customer.uuid, customerId: customer.id, street: 'Route 66'});
    console.log("\nAddress.create() ", createdAddress);

    // Retrieve address by ID to get the UUID of the address
    let address = await Address.findOne({
        where: { id: createdAddress.uuid } // <------ UUID instead of ID
    });
    console.log("\nAddress.findOne()", address);
    
    /*** Now check if the customer and addresses relations by uuid works ***/
    // Get customer with addresses relation
    let getCustomer = await Customer.findOne({
        where: { uuid: customer.uuid },
        include: { relation: 'addresses' }
    });
    console.log("\nCustomer with addresses relation ", getCustomer);

    // Get address with customer relation
    let getAddress = await Address.findOne({
        where: { uuid: address.uuid },
        include: { relation: 'customer' }
    });    
    
    console.log("\nAddress with customer relation ", getAddress);
}

These are the results

fasenderos · 2020-01-03T10:16:26Z

@fasenderos I think LB3 takes the id property as the source key by default. That's why when set the uuid property to id: true the relation works. I am not sure if lb3 can customize the source key. Can you try to set the primary key of relations as:

@agnes512 with your suggestions the create() method works right, but the relations don't. Here is my model def with your suggesstions:

Customer

{
  "name": "customer",
  "base": "PersistedModel",
  "idInjection": false,
  "options": {
    "validateUpsert": true
  },
  "properties": {
    "id": {
      "id": true,
      "generated": true,
      "type": "number"
    },
    "uuid": {      
      "type": "string",
      "defaultFn": "uuidv4"
    },
    "name": {
      "type": "string"
    }
  },
  "validations": [],
  "relations": {
    "addresses": {
      "model": "address",
      "type": "hasMany",
      "foreignKey": "customerId",
      "primaryKey": "uuid"
    }
  },
  "acls": [],
  "methods": {}
}

Address

{
  "name": "address",
  "base": "PersistedModel",
  "idInjection": false,
  "options": {
    "validateUpsert": true
  },
  "properties": {
    "id": {
      "id": true,
      "generated": true,
      "type": "number"      
    },
    "uuid": {      
      "type": "string",
      "defaultFn": "uuidv4"
    },
    "customerId": {
      "type": "number",
      "mysql": {
        "columnName": "customer_id"
      }
    },
    "customerUuid": {
      "type": "string",      
      "mysql": {
        "columnName": "customer_uuid"
      }
    },
    "street": {
      "type": "string"
    }
  },
  "validations": [],
  "relations": {
    "customer": {
      "model": "customer",
      "type": "belongsTo",
      "foreignKey": "customerId",
      "primaryKey": "uuid"
    }
  },
  "acls": [],
  "methods": {}
}

Edited boot script /boot/test.js

'use strict';

module.exports = async function (app) {
    let Customer = app.models.Customer;
    let Address = app.models.Address;

    // Create Customer
    let createdCustomer = await Customer.create({ name: 'John Doe' });
    console.log("Customer.create() ", createdCustomer);    

    /** NO MORE NEED to retrieve the customer because now we get the UUID **/

    // Create address with customer reference
    let createdAddress = await Address.create({customerUuid: createdCustomer.uuid, customerId: createdCustomer.id, street: 'Route 66'});
    console.log("\nAddress.create() ", createdAddress);

    /** NO MORE NEED to retrieve the address because now we get the UUID **/
    
    /*** Now check if customer and addresses relations by uuid works ***/
    // Get customer with addresses relation
    let getCustomer = await Customer.findOne({
        where: { uuid: createdCustomer.uuid },
        include: { relation: 'addresses' }
    });
    console.log("\nCustomer with addresses relation ", getCustomer);

    // Get address with customer relation
    let getAddress = await Address.findOne({
        where: { uuid: createdAddress.uuid },
        include: { relation: 'customer' }
    });    
    
    console.log("\nAddress with customer relation ", getAddress);
}

These are the results

agnes512 · 2020-01-03T14:07:30Z

@fasenderos sorry I made a mistake! Both relation definitions in my suggestion should be :

      "foreignKey": "customerUuid", // not customerId
      "primaryKey": "uuid"

Could you try it one more time? thanks!

uuid should be working fine when it's the id property. Just need to check if it's possible to customize the primary key in LB3 so that we can have both int type id and string type uuid.

fasenderos · 2020-01-03T14:27:56Z

@fasenderos sorry I made a mistake! Both relation definitions in my suggestion should be :
      "foreignKey": "customerUuid", // not customerId
      "primaryKey": "uuid"
Could you try it one more time? thanks!

uuid should be working fine when it's the id property. Just need to check if it's possible to customize the primary key in LB3 so that we can have both int type id and string type uuid.

@agnes512 great! Now it works, thanks for your help!

Here is the result

agnes512 · 2020-01-03T14:36:07Z

Great!! :D

As for your first definition, I think the problem happens when you do the creation.

customer.create({name: 'J D', uuid: 24});

Type of uuid is string. So it somehow assigns 24 to the int type id. And since the defaultFn of uuid is set, property uuid will be generated automatically, no need to set the value.

If you would like have auto increment id (as the id property) and auto-generated uuid, use the generated flag in your property definitions:

... customer mode def
  "properties": {
    "id": {
      "id": true,   // model primary key is still id 
      "generated": true,   // needed, but only works with int type id
      "type": "number"
    },
    "uuid": {
      "type": "string",
      "defaultFn": "uuidv4"
    },
   }..

with this definition, you can create instances without defining id or uuid:

customer.create({name: 'LoopBack'})

both id and uuid will be included in the response.

fasenderos · 2020-01-03T14:48:10Z

Great!! :D

As for your first definition, I think the problem happens when you do the creation.
customer.create({name: 'J D', uuid: 24});
Type of uuid is string. So it somehow assigns 24 to the int type id. And since the defaultFn of uuid is set, property uuid will be generated automatically, no need to set the value.

No no, I never create customer with the uuid prop. I always create customer only with the name but the response was an object with name and uuid (with the auto-incremented id - check the script at /boot/test.js here #4287 (comment))

However I understand the point. Thanks so much!

agnes512 · 2020-01-07T14:58:16Z

Feel free to re-open the issue in case it is not solved yet.

bajtos transferred this issue from loopbackio/loopback-next Nov 29, 2019

bajtos added the bug label Nov 29, 2019

bajtos added the juggler-relations label Nov 29, 2019

bajtos mentioned this issue Dec 5, 2019

Jan 2020 milestone 2️⃣0️⃣2️⃣0️⃣ loopbackio/loopback-next#4186

Closed

21 tasks

dhmlau added the 2020Q1 label Dec 5, 2019

agnes512 mentioned this issue Dec 11, 2019

fix: allow string type attribute to be auto-generated in Postgres loopbackio/loopback-connector-postgresql#404

Merged

4 tasks

dhmlau added this to the Jan 2020 milestone Dec 19, 2019

agnes512 closed this as completed Jan 7, 2020

emonddr mentioned this issue Jan 7, 2020

Monthly Milestone - January 2020 ❄️🎆🎉 loopbackio/loopback-next#4376

Closed

24 tasks

dhmlau assigned agnes512 Feb 10, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

lb3 - Relations on uuid #4287

lb3 - Relations on uuid #4287

fasenderos commented Nov 29, 2019

fasenderos commented Nov 29, 2019

bajtos commented Nov 29, 2019

bajtos commented Nov 29, 2019

fasenderos commented Nov 29, 2019

bajtos commented Nov 29, 2019

fasenderos commented Nov 29, 2019 •

edited

Loading

fasenderos commented Dec 2, 2019 •

edited

Loading

agnes512 commented Jan 2, 2020 •

edited

Loading

fasenderos commented Jan 3, 2020 •

edited

Loading

fasenderos commented Jan 3, 2020 •

edited

Loading

agnes512 commented Jan 3, 2020 •

edited

Loading

fasenderos commented Jan 3, 2020

agnes512 commented Jan 3, 2020

fasenderos commented Jan 3, 2020 •

edited

Loading

agnes512 commented Jan 7, 2020

lb3 - Relations on uuid #4287

lb3 - Relations on uuid #4287

Comments

fasenderos commented Nov 29, 2019

Steps to reproduce

Expected Behavior

Additional information

fasenderos commented Nov 29, 2019

bajtos commented Nov 29, 2019

bajtos commented Nov 29, 2019

fasenderos commented Nov 29, 2019

bajtos commented Nov 29, 2019

fasenderos commented Nov 29, 2019 • edited Loading

fasenderos commented Dec 2, 2019 • edited Loading

agnes512 commented Jan 2, 2020 • edited Loading

Customer

Address

fasenderos commented Jan 3, 2020 • edited Loading

fasenderos commented Jan 3, 2020 • edited Loading

agnes512 commented Jan 3, 2020 • edited Loading

fasenderos commented Jan 3, 2020

agnes512 commented Jan 3, 2020

fasenderos commented Jan 3, 2020 • edited Loading

agnes512 commented Jan 7, 2020

fasenderos commented Nov 29, 2019 •

edited

Loading

fasenderos commented Dec 2, 2019 •

edited

Loading

agnes512 commented Jan 2, 2020 •

edited

Loading

fasenderos commented Jan 3, 2020 •

edited

Loading

fasenderos commented Jan 3, 2020 •

edited

Loading

agnes512 commented Jan 3, 2020 •

edited

Loading

fasenderos commented Jan 3, 2020 •

edited

Loading