{"action":"created","issue":{"url":"https://api.github.com/repos/balderdashy/sails-postgresql/issues/110","labels_url":"https://api.github.com/repos/balderdashy/sails-postgresql/issues/110/labels{/name}","comments_url":"https://api.github.com/repos/balderdashy/sails-postgresql/issues/110/comments","events_url":"https://api.github.com/repos/balderdashy/sails-postgresql/issues/110/events","html_url":"https://github.com/balderdashy/sails-postgresql/issues/110","id":43259590,"number":110,"title":"Unexpected Behavior on 3-Level Nested One-to-Many Update ","user":{"login":"mchavarriagam","id":521519,"avatar_url":"https://avatars.githubusercontent.com/u/521519?v=3","gravatar_id":"","url":"https://api.github.com/users/mchavarriagam","html_url":"https://github.com/mchavarriagam","followers_url":"https://api.github.com/users/mchavarriagam/followers","following_url":"https://api.github.com/users/mchavarriagam/following{/other_user}","gists_url":"https://api.github.com/users/mchavarriagam/gists{/gist_id}","starred_url":"https://api.github.com/users/mchavarriagam/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mchavarriagam/subscriptions","organizations_url":"https://api.github.com/users/mchavarriagam/orgs","repos_url":"https://api.github.com/users/mchavarriagam/repos","events_url":"https://api.github.com/users/mchavarriagam/events{/privacy}","received_events_url":"https://api.github.com/users/mchavarriagam/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":4,"created_at":"2014-09-19T16:58:02Z","updated_at":"2015-03-18T16:03:37Z","closed_at":null,"body":"Hello,\r\n\r\nI ran into an issue while trying to do an update on a 3-level One-to-Many relation. Here's some code to set the stage:\r\n\r\nModel Human.js\r\n```\r\nmodule.exports = {\r\n  connection: 'postgres',\r\n  attributes: {\r\n    name: {\r\n      type: 'string',\r\n      required: true\r\n    },\r\n    pets: {\r\n      collection: 'pet',\r\n      via: 'human_id'\r\n    }\r\n  }\r\n};\r\n```\r\n\r\nModel Pet.js\r\n```\r\nmodule.exports = {\r\n  connection: 'postgres',\r\n  attributes: {\r\n    human_id: {\r\n      model: 'human'\r\n    },\r\n    name: {\r\n      type: 'string',\r\n      required: true\r\n    },\r\n    toys: {\r\n      collection: 'toy',\r\n      via: 'pet_id'\r\n    }\r\n  }\r\n};\r\n```\r\n\r\nModel Toy.js\r\n```\r\nmodule.exports = {\r\n  connection: 'postgres',\r\n  attributes: {\r\n    pet_id: {\r\n      model: 'pet'\r\n    },\r\n    name: {\r\n      type: 'string',\r\n      required: true\r\n    }\r\n  }\r\n};\r\n```\r\nI insert the Human record and its associated Pets (and their Associated Toys), by doing this (works as expected):\r\n```\r\nvar create_data = {\r\n   \"name\":\"mc\",\r\n   \"pets\":[\r\n      {\r\n         \"name\":\"Tanook\",\r\n         \"toys\":[\r\n            {\r\n               \"name\":\"Tunnel\"\r\n            },\r\n            {\r\n               \"name\":\"Pole\"\r\n            }\r\n         ]\r\n      },\r\n      {\r\n         \"name\":\"Emma\",\r\n         \"toys\":[\r\n            {\r\n               \"name\":\"Tower\"\r\n            },\r\n            {\r\n               \"name\":\"Fishy\"\r\n            }\r\n         ]\r\n      }\r\n   ]\r\n};\r\n\r\nHuman.create(create_data);\r\n// everything goes fine\r\n```\r\nThen when I try to update it and its associations (and their associations), like this:\r\n```\r\nvar update_data = {\r\n  \"id\": 1,\r\n  \"name\": \"mc\",\r\n  \"pets\": [\r\n    {\r\n      \"toys\": [\r\n        {\r\n          \"name\": \"Tunnel\",\r\n          \"id\": 1,\r\n          \"pet_id\": 1\r\n        },\r\n        {\r\n          \"name\": \"Pole\",\r\n          \"id\": 2,\r\n          \"pet_id\": 1\r\n        }\r\n      ],\r\n      \"human_id\": 1,\r\n      \"name\": \"Tanook\",\r\n      \"id\": 1\r\n    },\r\n    {\r\n      \"toys\": [\r\n        {\r\n          \"name\": \"Tower\",\r\n          \"id\": 3,\r\n          \"pet_id\": 2\r\n        },\r\n        {\r\n          \"name\": \"Fishy\",\r\n          \"id\": 4,\r\n          \"pet_id\": 2\r\n        }\r\n      ],\r\n      \"human_id\": 1,\r\n      \"name\": \"Emma\",\r\n      \"id\": 2\r\n    }\r\n  ]\r\n};\r\n```\r\n\r\nEven though I get no errors, this is what I get when I do a find one and populate (confirmed by looking at the database):\r\n```\r\n{\r\n  \"id\": 1,\r\n  \"name\": \"mc\",\r\n  \"pets\": [\r\n    {\r\n      \"toys\": [],\r\n      \"human_id\": 1,\r\n      \"name\": \"Tanook\",\r\n      \"id\": 1,\r\n      \"createdAt\": \"2014-09-19T16:48:52.369Z\",\r\n      \"updatedAt\": \"2014-09-19T16:49:06.145Z\"\r\n    },\r\n    {\r\n      \"toys\": [\r\n        {\r\n          \"name\": \"Tunnel\",\r\n          \"id\": 1,\r\n          \"createdAt\": \"2014-09-19T16:48:52.378Z\",\r\n          \"updatedAt\": \"2014-09-19T16:49:06.108Z\",\r\n          \"pet_id\": 2\r\n        },\r\n        {\r\n          \"name\": \"Pole\",\r\n          \"id\": 2,\r\n          \"createdAt\": \"2014-09-19T16:48:52.379Z\",\r\n          \"updatedAt\": \"2014-09-19T16:49:06.109Z\",\r\n          \"pet_id\": 2\r\n        },\r\n        {\r\n          \"name\": \"Tower\",\r\n          \"id\": 3,\r\n          \"createdAt\": \"2014-09-19T16:48:52.380Z\",\r\n          \"updatedAt\": \"2014-09-19T16:49:06.110Z\",\r\n          \"pet_id\": 2\r\n        },\r\n        {\r\n          \"name\": \"Fishy\",\r\n          \"id\": 4,\r\n          \"createdAt\": \"2014-09-19T16:48:52.381Z\",\r\n          \"updatedAt\": \"2014-09-19T16:49:06.110Z\",\r\n          \"pet_id\": 2\r\n        }\r\n      ],\r\n      \"human_id\": 1,\r\n      \"name\": \"Emma\",\r\n      \"id\": 2,\r\n      \"createdAt\": \"2014-09-19T16:48:52.370Z\",\r\n      \"updatedAt\": \"2014-09-19T16:49:06.146Z\"\r\n    }\r\n  ],\r\n  \"createdAt\": \"2014-09-19T16:48:52.335Z\",\r\n  \"updatedAt\": \"2014-09-19T16:49:06.143Z\"\r\n}\r\n```\r\ni.e. all third-level records (Toys) were assigned to just the first second-level record (Pet). Debugging the sails-postgresql adapter, this is what I noticed (log dump):\r\n```\r\n// makes sense, we're updating the human\r\n{ query: 'UPDATE \"human\" SET \"id\" = $1, \"name\" = $2, \"updatedAt\" = $3 WHERE \"human\".\"id\" = $4 RETURNING *',\r\n  values: [ 1, 'mc', '2014-09-19T16:53:24.847Z', 1 ] }\r\n// same, updating the pet\r\n{ query: 'UPDATE \"pet\" SET \"human_id\" = $1, \"name\" = $2, \"id\" = $3, \"updatedAt\" = $4 WHERE \"pet\".\"id\" = $5 RETURNING *',\r\n  values: [ 1, 'Tanook', 1, '2014-09-19T16:53:24.850Z', 1 ] }\r\n// same, updating the pet\r\n{ query: 'UPDATE \"pet\" SET \"human_id\" = $1, \"name\" = $2, \"id\" = $3, \"updatedAt\" = $4 WHERE \"pet\".\"id\" = $5 RETURNING *',\r\n  values: [ 1, 'Emma', 2, '2014-09-19T16:53:24.851Z', 2 ] }\r\n// same, updating the toy\r\n{ query: 'UPDATE \"toy\" SET \"name\" = $1, \"id\" = $2, \"pet_id\" = $3, \"updatedAt\" = $4 WHERE \"toy\".\"id\" = $5 RETURNING *',\r\n  values: [ 'Tunnel', 1, 1, '2014-09-19T16:53:24.854Z', 1 ] }\r\n// same, updating the toy\r\n{ query: 'UPDATE \"toy\" SET \"name\" = $1, \"id\" = $2, \"pet_id\" = $3, \"updatedAt\" = $4 WHERE \"toy\".\"id\" = $5 RETURNING *',\r\n  values: [ 'Pole', 2, 1, '2014-09-19T16:53:24.854Z', 2 ] }\r\n// same, updating the toy\r\n{ query: 'UPDATE \"toy\" SET \"name\" = $1, \"id\" = $2, \"pet_id\" = $3, \"updatedAt\" = $4 WHERE \"toy\".\"id\" = $5 RETURNING *',\r\n  values: [ 'Tower', 3, 2, '2014-09-19T16:53:24.855Z', 3 ] }\r\n// same, updating the toy\r\n{ query: 'UPDATE \"toy\" SET \"name\" = $1, \"id\" = $2, \"pet_id\" = $3, \"updatedAt\" = $4 WHERE \"toy\".\"id\" = $5 RETURNING *',\r\n  values: [ 'Fishy', 4, 2, '2014-09-19T16:53:24.856Z', 4 ] }\r\n// By now we're done with updating the human, pets and toys.\r\n// What is this? Why null?\r\n{ query: 'UPDATE \"toy\" SET \"pet_id\" = $1, \"updatedAt\" = $2 WHERE \"toy\".\"pet_id\" = $3 RETURNING *',\r\n  values: [ null, '2014-09-19T16:53:24.858Z', 2 ] }\r\n// What is this? Why null?\r\n{ query: 'UPDATE \"toy\" SET \"pet_id\" = $1, \"updatedAt\" = $2 WHERE \"toy\".\"pet_id\" = $3 RETURNING *',\r\n  values: [ null, '2014-09-19T16:53:24.860Z', 2 ] }\r\n// And what is this now? From here on it's unnecessary stuff, I think.\r\n{ query: 'UPDATE \"pet\" SET \"human_id\" = $1, \"name\" = $2, \"id\" = $3, \"createdAt\" = $4, \"updatedAt\" = $5 WHERE \"pet\".\"id\" = $6 RETURNING *',\r\n  values: \r\n   [ 1,\r\n     'Emma',\r\n     2,\r\n     '2014-09-19T16:53:13.350Z',\r\n     '2014-09-19T16:53:24.862Z',\r\n     2 ] }\r\n{ query: 'UPDATE \"pet\" SET \"human_id\" = $1, \"name\" = $2, \"id\" = $3, \"createdAt\" = $4, \"updatedAt\" = $5 WHERE \"pet\".\"id\" = $6 RETURNING *',\r\n  values: \r\n   [ 1,\r\n     'Emma',\r\n     2,\r\n     '2014-09-19T16:53:13.350Z',\r\n     '2014-09-19T16:53:24.865Z',\r\n     2 ] }\r\n{ query: 'UPDATE \"pet\" SET \"human_id\" = $1, \"name\" = $2, \"id\" = $3, \"createdAt\" = $4, \"updatedAt\" = $5 WHERE \"pet\".\"id\" = $6 RETURNING *',\r\n  values: \r\n   [ 1,\r\n     'Emma',\r\n     2,\r\n     '2014-09-19T16:53:13.350Z',\r\n     '2014-09-19T16:53:24.866Z',\r\n     2 ] }\r\n{ query: 'UPDATE \"pet\" SET \"human_id\" = $1, \"name\" = $2, \"id\" = $3, \"createdAt\" = $4, \"updatedAt\" = $5 WHERE \"pet\".\"id\" = $6 RETURNING *',\r\n  values: \r\n   [ 1,\r\n     'Emma',\r\n     2,\r\n     '2014-09-19T16:53:13.350Z',\r\n     '2014-09-19T16:53:24.868Z',\r\n     2 ] }\r\n{ query: 'UPDATE \"toy\" SET \"pet_id\" = $1, \"updatedAt\" = $2 WHERE \"toy\".\"id\" = $3 RETURNING *',\r\n  values: [ 2, '2014-09-19T16:53:24.871Z', 1 ] }\r\n{ query: 'UPDATE \"toy\" SET \"pet_id\" = $1, \"updatedAt\" = $2 WHERE \"toy\".\"id\" = $3 RETURNING *',\r\n  values: [ 2, '2014-09-19T16:53:24.871Z', 2 ] }\r\n{ query: 'UPDATE \"toy\" SET \"pet_id\" = $1, \"updatedAt\" = $2 WHERE \"toy\".\"id\" = $3 RETURNING *',\r\n  values: [ 2, '2014-09-19T16:53:24.872Z', 3 ] }\r\n{ query: 'UPDATE \"toy\" SET \"pet_id\" = $1, \"updatedAt\" = $2 WHERE \"toy\".\"id\" = $3 RETURNING *',\r\n  values: [ 2, '2014-09-19T16:53:24.873Z', 4 ] }\r\n{ query: 'UPDATE \"toy\" SET \"pet_id\" = $1, \"updatedAt\" = $2 WHERE \"toy\".\"id\" = $3 RETURNING *',\r\n  values: [ 2, '2014-09-19T16:53:24.892Z', 1 ] }\r\n{ query: 'UPDATE \"toy\" SET \"pet_id\" = $1, \"updatedAt\" = $2 WHERE \"toy\".\"id\" = $3 RETURNING *',\r\n  values: [ 2, '2014-09-19T16:53:24.893Z', 2 ] }\r\n{ query: 'UPDATE \"toy\" SET \"pet_id\" = $1, \"updatedAt\" = $2 WHERE \"toy\".\"id\" = $3 RETURNING *',\r\n  values: [ 2, '2014-09-19T16:53:24.894Z', 3 ] }\r\n{ query: 'UPDATE \"toy\" SET \"pet_id\" = $1, \"updatedAt\" = $2 WHERE \"toy\".\"id\" = $3 RETURNING *',\r\n  values: [ 2, '2014-09-19T16:53:24.895Z', 4 ] }\r\n{ query: 'UPDATE \"pet\" SET \"human_id\" = $1, \"updatedAt\" = $2 WHERE \"pet\".\"human_id\" = $3 RETURNING *',\r\n  values: [ null, '2014-09-19T16:53:24.916Z', 1 ] }\r\n{ query: 'UPDATE \"human\" SET \"name\" = $1, \"id\" = $2, \"createdAt\" = $3, \"updatedAt\" = $4 WHERE \"human\".\"id\" = $5 RETURNING *',\r\n  values: \r\n   [ 'mc',\r\n     1,\r\n     '2014-09-19T16:53:13.319Z',\r\n     '2014-09-19T16:53:24.919Z',\r\n     1 ] }\r\n{ query: 'UPDATE \"human\" SET \"name\" = $1, \"id\" = $2, \"createdAt\" = $3, \"updatedAt\" = $4 WHERE \"human\".\"id\" = $5 RETURNING *',\r\n  values: \r\n   [ 'mc',\r\n     1,\r\n     '2014-09-19T16:53:13.319Z',\r\n     '2014-09-19T16:53:24.922Z',\r\n     1 ] }\r\n{ query: 'UPDATE \"pet\" SET \"human_id\" = $1, \"updatedAt\" = $2 WHERE \"pet\".\"id\" = $3 RETURNING *',\r\n  values: [ 1, '2014-09-19T16:53:24.925Z', 1 ] }\r\n{ query: 'UPDATE \"pet\" SET \"human_id\" = $1, \"updatedAt\" = $2 WHERE \"pet\".\"id\" = $3 RETURNING *',\r\n  values: [ 1, '2014-09-19T16:53:24.926Z', 2 ] }\r\n```\r\n\r\nI also tested this by switching the adapter to localDiskDb and it worked so I'm assuming it's a sails-postgresql issue.\r\n\r\nThe funny thing is that the nested update works, it's just that it breaks *after* the update is done because the internals keep doing extra updates (not sure why).\r\n\r\nAny ideas? Is this an actual bug or am I doing something wrong?"},"comment":{"url":"https://api.github.com/repos/balderdashy/sails-postgresql/issues/comments/83035594","html_url":"https://github.com/balderdashy/sails-postgresql/issues/110#issuecomment-83035594","issue_url":"https://api.github.com/repos/balderdashy/sails-postgresql/issues/110","id":83035594,"user":{"login":"mchavarriagam","id":521519,"avatar_url":"https://avatars.githubusercontent.com/u/521519?v=3","gravatar_id":"","url":"https://api.github.com/users/mchavarriagam","html_url":"https://github.com/mchavarriagam","followers_url":"https://api.github.com/users/mchavarriagam/followers","following_url":"https://api.github.com/users/mchavarriagam/following{/other_user}","gists_url":"https://api.github.com/users/mchavarriagam/gists{/gist_id}","starred_url":"https://api.github.com/users/mchavarriagam/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mchavarriagam/subscriptions","organizations_url":"https://api.github.com/users/mchavarriagam/orgs","repos_url":"https://api.github.com/users/mchavarriagam/repos","events_url":"https://api.github.com/users/mchavarriagam/events{/privacy}","received_events_url":"https://api.github.com/users/mchavarriagam/received_events","type":"User","site_admin":false},"created_at":"2015-03-18T16:03:37Z","updated_at":"2015-03-18T16:03:37Z","body":"Nothing. I ended up switching to a different framework/solution for my project. Sorry."}}