Strongloop PostgreSQL connector embedded model error

1
votes

Got a simple Loopback API to retrieve tickets and responses using the postgreSQL connector. Tickets and responses come back fine individually but when I try to embed the responses into the ticket model I get the error below. I have tried following the documentation and I'm sure it's something simple in one of my relations that I'm missing but whatever I try, I cannot get it to work.

Any help would be appreciated.

https://docs.strongloop.com/display/public/LB/Embedded+models+and+relations#Embeddedmodelsandrelations-EmbedsMany

Ticket Model:

{
"name": "Ticket",          
"base": "PersistedModel",
"idInjection": true,
"options": {
    "postgresql": {
        "schema": "customer_service",
        "table": "tbl_ticket"
     }
},
"properties": {
    "description": {
      "type": "String",
      "required": true,
      "length": null,
      "precision": null,
      "scale": null,
      "postgresql": {
        "columnName": "description",
        "dataType": "text",
        "dataLength": null,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "NO"
      }
    },    
    "id": {
      "type": "Number",
      "id": 1,
      "required": true,
      "length": null,
      "precision": 32,
      "scale": 0,
      "postgresql": {
        "columnName": "id",
        "dataType": "integer",
        "dataLength": null,
        "dataPrecision": 32,
        "dataScale": 0,
        "nullable": "NO"
      }
    }
  },
  "validations": [],
  "relations": {
    "responses": {
      "type": "embedsMany",
      "model": "Response",
      "property": "embededResponses",
      "options": {
        "validate": true,
        "forceId": false
      }   
    }
  },
  "acls": [],
  "methods": {}
}

Response Model:

{
  "name": "Response",
  "base": "PersistedModel",
  "idInjection": true,  
  "options": {
    "postgresql": {
      "schema": "customer_service",
      "table": "tbl_response"
    }
  },
  "properties": {
    "notes": {
      "type": "String",
      "required": false,
      "length": null,
      "precision": null,
      "scale": null,
      "postgresql": {
        "columnName": "notes",
        "dataType": "text",
        "dataLength": null,
        "dataPrecision": null,
        "dataScale": null,
        "nullable": "YES"
      }
    },
    "ticketId": {
      "type": "Number",
      "required": true,
      "length": null,
      "precision": 32,
      "scale": 0,
      "postgresql": {
        "columnName": "ticket_id",
        "dataType": "integer",
        "dataLength": null,
        "dataPrecision": 32,
        "dataScale": 0,
        "nullable": "NO"
      }
    },
    "id": {
      "type": "Number",
      "id": 1,
      "required": true,
      "length": null,
      "precision": 32,
      "scale": 0,
      "postgresql": {
        "columnName": "id",
        "dataType": "integer",
        "dataLength": null,
        "dataPrecision": 32,
        "dataScale": 0,
        "nullable": "NO"
      }
    }
  },
  "validations": [],
  "relations": {
    "ticket": {
      "type": "belongsTo",
      "model": "Ticket",
      "foreignKey": "ticketId"
    }
  },
  "acls": [],
  "methods": {}
}

Error:

{  
    "error": {
      "name": "error",
      "status": 500,
      "message": "column \"embededresponses\" does not exist",
      "length": 126,
      "severity": "ERROR",
      "code": "42703",
      "position": "213",
      "file": ".\\src\\backend\\parser\\parse_expr.c",
      "line": "766",
      "routine": "transformColumnRef",
      "stack": "error: column \"embededresponses\" does not exist\n    at Connection.parseE (C:\\WebApp\\node_modules\\loopback-connector-postgresql\\node_modules\\pg\\lib\\connection.js:539:11)\n    at Connection.parseMessage (C:\\WebApp\\node_modules\\loopback-connector-postgresql\\node_modules\\pg\\lib\\connection.js:366:17)\n    at Socket.<anonymous> (C:\\WebApp\\node_modules\\loopback-connector-postgresql\\node_modules\\pg\\lib\\connection.js:105:22)\n    at Socket.emit (events.js:107:17)\n    at readableAddChunk (_stream_readable.js:163:16)\n    at Socket.Readable.push (_stream_readable.js:126:10)\n    at TCP.onread (net.js:538:20)"
    }
}
2
loopbackjsstrongloop
I don't see the embededResponses property in that model. The error seems to show this is not existing. - JSimonsen
I did try that by declaring an embededResponses property to my ticket model as both an object and an array but still get the same error. It doesn't look like you need to do that if you look at their example on GitHub. github.com/strongloop/loopback-example-embedded-relations/blob/… - jdavis89

2 Answers

1
votes

Your Ticket model should have the following relationship section:

"relations": {
    "Responses": {
      "type": "hasMany",
      "model": "Response",
      "foreignKey": "ticketId"
    }
  }

Your Response model relationship is correct.

It's not very clear from the docs that the embed relationships are for NoSQL databases. For traditional SQL databases, use the Has* relationship types.

To retrieve a Ticket with Responses from the REST API use the include filter: https://docs.strongloop.com/display/public/LB/Include+filter.

Example: localhost:3000/api/Tickets/{id}?filter[include]=responses

0
votes

I am told that you can use embedded relations with SQL data sources, but the data is then stored in stringified-JSON format.

I've added a note to https://docs.strongloop.com/display/LB/Embedded+models+and+relations.

Rand