{"action":"created","issue":{"url":"https://api.github.com/repos/vesper-framework/vesper/issues/13","repository_url":"https://api.github.com/repos/vesper-framework/vesper","labels_url":"https://api.github.com/repos/vesper-framework/vesper/issues/13/labels{/name}","comments_url":"https://api.github.com/repos/vesper-framework/vesper/issues/13/comments","events_url":"https://api.github.com/repos/vesper-framework/vesper/issues/13/events","html_url":"https://github.com/vesper-framework/vesper/issues/13","id":315065772,"number":13,"title":"Not working with composite primary key","user":{"login":"icepeng","id":12782606,"avatar_url":"https://avatars0.githubusercontent.com/u/12782606?v=4","gravatar_id":"","url":"https://api.github.com/users/icepeng","html_url":"https://github.com/icepeng","followers_url":"https://api.github.com/users/icepeng/followers","following_url":"https://api.github.com/users/icepeng/following{/other_user}","gists_url":"https://api.github.com/users/icepeng/gists{/gist_id}","starred_url":"https://api.github.com/users/icepeng/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/icepeng/subscriptions","organizations_url":"https://api.github.com/users/icepeng/orgs","repos_url":"https://api.github.com/users/icepeng/repos","events_url":"https://api.github.com/users/icepeng/events{/privacy}","received_events_url":"https://api.github.com/users/icepeng/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":3,"created_at":"2018-04-17T13:42:39Z","updated_at":"2018-04-19T15:32:32Z","closed_at":null,"author_association":"NONE","body":"I am trying to use vesper with my existing project, but it does not return entities that has composite primary key.\r\n\r\nhttps://github.com/icepeng/oriental-vesper\r\n\r\nHere is my repo, the `card-response`, `card-stats` entity has a problem.\r\n\r\nThis is example query\r\n```\r\nquery {\r\n  expansions {\r\n    code\r\n    cards {\r\n      id\r\n      stats {\r\n        survey {\r\n          id\r\n        }\r\n        data {\r\n          power\r\n          generality\r\n          responseCount\r\n        }\r\n      }\r\n    }\r\n  }\r\n}\r\n```\r\n\r\nAnd this is response\r\n```\r\n{\r\n  \"data\": {\r\n    \"expansions\": [\r\n      {\r\n        \"code\": \"the-witchwood\",\r\n        \"cards\": [\r\n          {\r\n            \"id\": \"black-cat\",\r\n            \"stats\": []\r\n          },\r\n          {\r\n            \"id\": \"vex-crow\",\r\n            \"stats\": []\r\n          },\r\n          {\r\n            \"id\": \"cinderstorm\",\r\n            \"stats\": []\r\n          },\r\n        ]\r\n      }\r\n    ]\r\n  }\r\n}\r\n```\r\n\r\nAnd this is logged typeorm generated query\r\n```\r\nquery: SELECT \"Expansion\".\"code\" AS \"Expansion_code\" FROM \"expansion\" \"Expansion\"\r\nquery: SELECT \"cards\".\"id\" AS \"cards_id\", \"cards\".\"cost\" AS \"cards_cost\", \"cards\".\"class\" AS \"cards_class\", \"cards\".\"rarity\" AS \"cards_rarity\", \"cards\".\"expansionCode\" AS \"cards_expansionCode\" FROM \"card\" \"cards\" WHERE \"cards\".\"expansionCode\" IN ($1) -- PARAMETERS: [\"the-witchwood\"]\r\nquery: SELECT \"Card\".\"id\" AS \"Card_id\", \"Card\".\"expansionCode\" AS \"Expansion_code\" FROM \"card\" \"Card\" WHERE \"Card\".\"expansionCode\" IN ($1) -- PARAMETERS: [\"the-witchwood\"]\r\nquery: SELECT \"stats\".\"cardId\" AS \"stats_cardId\", \"stats\".\"data\" AS \"stats_data\", \"stats\".\"surveyId\" AS \"stats_surveyId\" FROM \"card_stat\" \"stats\" WHERE \"stats\".\"cardId\" IN ($1, $2, $3) -- PARAMETERS: [\"black-cat\",\"vex-crow\",\"cinderstorm\"]\r\nquery: SELECT \"CardStat\".\"cardId\" AS \"CardStat_cardId\", \"CardStat\".\"surveyId\" AS \"CardStat_survey_id\" FROM \"card_stat\" \"CardStat\" WHERE \"CardStat\".\"cardId\" IN ($1, $2, $3) -- PARAMETERS: [\"black-cat\",\"vex-crow\",\"cinderstorm\"]\r\n```"},"comment":{"url":"https://api.github.com/repos/vesper-framework/vesper/issues/comments/382780345","html_url":"https://github.com/vesper-framework/vesper/issues/13#issuecomment-382780345","issue_url":"https://api.github.com/repos/vesper-framework/vesper/issues/13","id":382780345,"user":{"login":"josephktcheung","id":4270232,"avatar_url":"https://avatars2.githubusercontent.com/u/4270232?v=4","gravatar_id":"","url":"https://api.github.com/users/josephktcheung","html_url":"https://github.com/josephktcheung","followers_url":"https://api.github.com/users/josephktcheung/followers","following_url":"https://api.github.com/users/josephktcheung/following{/other_user}","gists_url":"https://api.github.com/users/josephktcheung/gists{/gist_id}","starred_url":"https://api.github.com/users/josephktcheung/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/josephktcheung/subscriptions","organizations_url":"https://api.github.com/users/josephktcheung/orgs","repos_url":"https://api.github.com/users/josephktcheung/repos","events_url":"https://api.github.com/users/josephktcheung/events{/privacy}","received_events_url":"https://api.github.com/users/josephktcheung/received_events","type":"User","site_admin":false},"created_at":"2018-04-19T15:32:32Z","updated_at":"2018-04-19T15:32:32Z","author_association":"CONTRIBUTOR","body":"After further study, I believe the problem is from typeorm's [`RelationIdLoader`](https://github.com/typeorm/typeorm/blob/6cf206c9c977cec7c6121926cbbef251e2f6a31f/src/query-builder/RelationIdLoader.ts) and [`SelectQueryBuilder`](https://github.com/typeorm/typeorm/blob/b5161e82294352eec8e9d82ebe28f214998d18d2/src/query-builder/SelectQueryBuilder.ts).\r\n\r\nI made a controlled experiment by making 2 branches. 1st branch is the experimental (problematic) one - composite primary key branch https://github.com/josephktcheung/typescript-advanced-example/tree/feature/composite. I add `Author` entity and make `categoryId` and `authorId` to be composite primary key of `Post` in this branch.\r\n\r\n```ts\r\n// Category.ts\r\nimport { Column, Entity, OneToMany, PrimaryGeneratedColumn } from \"typeorm\";\r\nimport { Post } from \"./Post\";\r\n\r\n@Entity()\r\nexport class Category {\r\n\r\n    @PrimaryGeneratedColumn()\r\n    id: number;\r\n\r\n    @Column()\r\n    name: string;\r\n\r\n    @OneToMany(() => Post, post => post.category)\r\n    posts: Post[];\r\n\r\n}\r\n\r\n// Author.ts\r\nimport { Column, Entity, OneToMany, PrimaryGeneratedColumn } from \"typeorm\";\r\nimport { Post } from \"./Post\";\r\n\r\n@Entity()\r\nexport class Author {\r\n\r\n  @PrimaryGeneratedColumn()\r\n  id: number;\r\n\r\n  @Column()\r\n  name: string;\r\n\r\n  @OneToMany(() => Post, post => post.author)\r\n  posts: Post[];\r\n\r\n}\r\n\r\n// Post.ts\r\nimport { Column, Entity, JoinTable, ManyToMany, PrimaryGeneratedColumn, ManyToOne } from \"typeorm\";\r\nimport { Category } from \"./Category\";\r\nimport { Author } from \"./Author\";\r\n\r\n@Entity()\r\nexport class Post {\r\n\r\n    @Column()\r\n    title: string;\r\n\r\n    @Column()\r\n    text: string;\r\n\r\n    @ManyToOne(() => Category, category => category.posts, { primary: true })\r\n    category: Category;\r\n\r\n    @ManyToOne(() => Author, author => author.posts, { primary: true })\r\n    author: Author;\r\n\r\n    categoryNames: string[];\r\n}\r\n```\r\n\r\n\r\nThe controlled branch (work normally) is https://github.com/josephktcheung/typescript-advanced-example/tree/feature/one_to_many.  I set category <=> post to be 1:M relationship in this branch.\r\n```ts\r\n// Category.ts\r\nimport { Column, Entity, ManyToMany, PrimaryGeneratedColumn, OneToMany } from \"typeorm\";\r\nimport { Post } from \"./Post\";\r\n\r\n@Entity()\r\nexport class Category {\r\n\r\n    @PrimaryGeneratedColumn()\r\n    id: number;\r\n\r\n    @Column()\r\n    name: string;\r\n\r\n    @OneToMany(() => Post, post => post.category)\r\n    posts: Post[];\r\n\r\n}\r\n\r\n// Post.ts\r\nimport { Column, Entity, JoinTable, ManyToMany, PrimaryGeneratedColumn, ManyToOne } from \"typeorm\";\r\nimport { Category } from \"./Category\";\r\n\r\n@Entity()\r\nexport class Post {\r\n\r\n    @PrimaryGeneratedColumn()\r\n    id: number;\r\n\r\n    @Column()\r\n    title: string;\r\n\r\n    @Column()\r\n    text: string;\r\n\r\n    @ManyToOne(() => Category, category => category.posts)\r\n    category: Category;\r\n\r\n    categoryNames: string[];\r\n\r\n}\r\n```\r\n\r\nI fetch the same query in both branches:\r\n```gql\r\n{\r\n  categories {\r\n    id\r\n    name\r\n    posts {\r\n      text\r\n    }\r\n  }\r\n}\r\n```\r\n\r\nIf we turn on typeorm's logging, this is what's logged when I fetch the one to many branch:\r\n```\r\nquery: SELECT \"Post\".\"id\" AS \"Post_id\", \"Post\".\"categoryId\" AS \"Category_id\" FROM \"post\" \"Post\" WHERE \"Post\".\"categoryId\" IN (?, ?) -- PARAMETERS: [1,2]\r\n\r\nquery result: [ { Post_id: 1, Category_id: 1 }, { Post_id: 2, Category_id: 2 } ]\r\n```\r\n\r\nYou can see that `\"Post\".\"categoryId\"` is selected as `Category_id` here.\r\n\r\nAnd this is what's logged when I fetch the composite primary keys branch:\r\n```\r\nquery: SELECT \"Post\".\"categoryId\" AS \"Post_category_id\", \"Post\".\"authorId\" AS \"Post_author_id\" FROM \"post\" \"Post\" WHERE \"Post\".\"categoryId\" IN (?, ?, ?) -- PARAMETERS: [1,2,3]\r\n\r\nquery result: [ { Post_category_id: 1, Post_author_id: 1 } ]\r\n```\r\n\r\nYou can see that `\"Post\".\"categoryId\"` is selected as `Post_category_id` here.\r\n\r\nSince `RelationIdLoader` in this [line](https://github.com/typeorm/typeorm/blob/6cf206c9c977cec7c6121926cbbef251e2f6a31f/src/query-builder/RelationIdLoader.ts#L117) tries to find matched entities by using key `Category_id`, it can find matched entity in 1st query result while fail to find matched entity in 2nd query result.\r\n\r\nThe desired query to be generated in composite primary key case should be:\r\n```\r\nquery: SELECT \"Post\".\"categoryId\" AS \"Post_category_id\", \"Post\".\"authorId\" AS \"Post_author_id\", \"Post\".\"categoryId\" AS \"Category_id\" FROM \"post\" \"Post\" WHERE \"Post\".\"categoryId\" IN (?, ?, ?) -- PARAMETERS: [1,2,3]\r\n```\r\n\r\nThe reason why `RelationIdLoader` generates a \"wrong\" query is that when we are using composite primary keys, one of the primary columns and the join column are the same - \"Post\".\"categoryId\". \r\n\r\nWhen `RelationIdLoader` generates query [here](https://github.com/typeorm/typeorm/blob/6cf206c9c977cec7c6121926cbbef251e2f6a31f/src/query-builder/RelationIdLoader.ts#L291-L298), the path `\"Post\".\"CategoryId\"`  is selected twice:\r\n1. Selected as `Post_category_id` when `relation.entityMetadata.primaryColumns` is iterated [here](https://github.com/typeorm/typeorm/blob/6cf206c9c977cec7c6121926cbbef251e2f6a31f/src/query-builder/RelationIdLoader.ts#L291-L294)\r\n2. Selected as `Category_id` when `relation.joinColumns` is iterated [here](https://github.com/typeorm/typeorm/blob/6cf206c9c977cec7c6121926cbbef251e2f6a31f/src/query-builder/RelationIdLoader.ts#L295-L298)\r\n\r\nThis is the query builder's `expressionMap`'s `selects` after the iteration:\r\n```js\r\n{\r\nselects:\r\n   [ { selection: 'Post.category.id', aliasName: 'Post_category_id' },\r\n     { selection: 'Post.author.id', aliasName: 'Post_author_id' },\r\n     { selection: 'Post.category.id', aliasName: 'Category_id' } ]\r\n}\r\n```\r\n\r\nSince typeorm only select the first match `{ selection: 'Post.category.id', aliasName: 'Post_category_id' }` by using `Array.find` [here](https://github.com/typeorm/typeorm/blob/b5161e82294352eec8e9d82ebe28f214998d18d2/src/query-builder/SelectQueryBuilder.ts#L1658) when it builds the query, selection `{ selection: 'Post.category.id', aliasName: 'Category_id' }` is skipped, therefore `Category_id` column is not returned.\r\n\r\nSo to summarize:\r\n1. `RelationIdLoader` is used to [find related entities](https://github.com/typeorm/typeorm/issues/1608#issuecomment-367296721)\r\n2. When we use composite primary keys and find posts that belongs to a category, `RelationIdLoader` selects `\"Post\".\"CategoryId\"` twice, 1 for primary column and 1 for join column\r\n3. When typeorm builds the query, it always use the 1st selection which is the primary column selection, therefore the join column selection is omitted in the generated SQL.\r\n4. Because the column name used by `RelationIdLoader` to determine if there's entity match (`Category_id`) is different from the one returned from query (`Post_category_id` from primary column selection), `RelationIdLoader` cannot return related entities result correctly.\r\n\r\nHope the above explanation makes sense :)\r\n\r\n@pleerock what do you think? Can typeorm fix this by allowing same selection can be aliased multiple times so that it can generate a query like this when same selection path is selected using different aliases:\r\n```sql\r\nSELECT \"Post\".\"authorId\" AS \"Post_author_id\",  \"Post\".\"categoryId\" AS \"Post_category_id\", \"Post\".\"categoryId\" AS \"Category_id\" FROM \"post\" \"Post\" WHERE \"Post\".\"categoryId\" IN (1,2,3)\r\n```\r\nThen the `RelationIdLoader` can grab the correct column to return related entities."}}
{"actor":{"display_login":"josephktcheung"}}