Strongloop PostgreSQL connector embedded model error

Question

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)"
    }
}

Answer 1

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

Answer 2

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