Not working with composite primary key

[icepeng]

I am trying to use vesper with my existing project, but it does not return entities that has composite primary key.

https://github.com/icepeng/oriental-vesper

Here is my repo, the card-response, card-stats entity has a problem.

This is example query

query {
  expansions {
    code
    cards {
      id
      stats {
        survey {
          id
        }
        data {
          power
          generality
          responseCount
        }
      }
    }
  }
}

And this is response

{
  "data": {
    "expansions": [
      {
        "code": "the-witchwood",
        "cards": [
          {
            "id": "black-cat",
            "stats": []
          },
          {
            "id": "vex-crow",
            "stats": []
          },
          {
            "id": "cinderstorm",
            "stats": []
          },
        ]
      }
    ]
  }
}

And this is logged typeorm generated query

query: SELECT "Expansion"."code" AS "Expansion_code" FROM "expansion" "Expansion"
query: 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"]
query: SELECT "Card"."id" AS "Card_id", "Card"."expansionCode" AS "Expansion_code" FROM "card" "Card" WHERE "Card"."expansionCode" IN ($1) -- PARAMETERS: ["the-witchwood"]
query: 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"]
query: 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"]

[josephktcheung]

I'd like to know if it's possible to use composite primary keys in vesper as well. I work around it by setting a composite unique index on join columns and a PrimaryGeneratedColumn for id.

[icepeng]

@josephktcheung It could be a workaround, but it requires more find query before persist.

Benefit of using composite key in typeorm is that you can easily implement PUT method when entities for primary keys are served.

const stat = repository.create({
  card, // primary
  survey, // primary
  data
})
await repository.save(stat);

From this way, you can easily persist stat entity to my database without checking duplicate.

If there are no duplicate, it will create a new row. Else, it will update a existing row.

However, using unique index and other generated primary key make things dirty.

You have to [Check duplicate] -> [If not, create. Else, merge and update.]

[josephktcheung]

I see. I haven't tried that before in typeorm. I split my update and insert mutation in my graphql for joint table so I don't need upsert.

Perhaps you can write a custom resolver to resolve stats field?

[josephktcheung]

After further study, I believe the problem comes from typeorm's RelationIdLoader and SelectQueryBuilder.

I 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 keys of Post in this branch.

// Category.ts
import { Column, Entity, OneToMany, PrimaryGeneratedColumn } from "typeorm";
import { Post } from "./Post";

@Entity()
export class Category {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @OneToMany(() => Post, post => post.category)
    posts: Post[];

}

// Author.ts
import { Column, Entity, OneToMany, PrimaryGeneratedColumn } from "typeorm";
import { Post } from "./Post";

@Entity()
export class Author {

  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  name: string;

  @OneToMany(() => Post, post => post.author)
  posts: Post[];

}

// Post.ts
import { Column, Entity, JoinTable, ManyToMany, PrimaryGeneratedColumn, ManyToOne } from "typeorm";
import { Category } from "./Category";
import { Author } from "./Author";

@Entity()
export class Post {

    @Column()
    title: string;

    @Column()
    text: string;

    @ManyToOne(() => Category, category => category.posts, { primary: true })
    category: Category;

    @ManyToOne(() => Author, author => author.posts, { primary: true })
    author: Author;

    categoryNames: string[];
}

The 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.

// Category.ts
import { Column, Entity, ManyToMany, PrimaryGeneratedColumn, OneToMany } from "typeorm";
import { Post } from "./Post";

@Entity()
export class Category {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @OneToMany(() => Post, post => post.category)
    posts: Post[];

}

// Post.ts
import { Column, Entity, JoinTable, ManyToMany, PrimaryGeneratedColumn, ManyToOne } from "typeorm";
import { Category } from "./Category";

@Entity()
export class Post {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    title: string;

    @Column()
    text: string;

    @ManyToOne(() => Category, category => category.posts)
    category: Category;

    categoryNames: string[];

}

I fetch the same query in both branches:

{
  categories {
    id
    name
    posts {
      text
    }
  }
}

If we turn on typeorm's logging, this is what's logged when I fetch the one to many branch:

query: SELECT "Post"."id" AS "Post_id", "Post"."categoryId" AS "Category_id" FROM "post" "Post" WHERE "Post"."categoryId" IN (?, ?) -- PARAMETERS: [1,2]

query result: [ { Post_id: 1, Category_id: 1 }, { Post_id: 2, Category_id: 2 } ]

You can see that "Post"."categoryId" is selected as Category_id here.

And this is what's logged when I fetch the composite primary keys branch:

query: SELECT "Post"."categoryId" AS "Post_category_id", "Post"."authorId" AS "Post_author_id" FROM "post" "Post" WHERE "Post"."categoryId" IN (?, ?, ?) -- PARAMETERS: [1,2,3]

query result: [ { Post_category_id: 1, Post_author_id: 1 } ]

You can see that "Post"."categoryId" is selected as Post_category_id here.

Since RelationIdLoader in this line 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.

The desired query to be generated in composite primary key case should be:

query: 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]

The 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".

When RelationIdLoader generates query here, the path "Post"."CategoryId" is selected twice:

1. Selected as Post_category_id when relation.entityMetadata.primaryColumns is iterated here
2. Selected as Category_id when relation.joinColumns is iterated here

This is the query builder's expressionMap's selects after the iteration:

{
selects:
   [ { selection: 'Post.category.id', aliasName: 'Post_category_id' },
     { selection: 'Post.author.id', aliasName: 'Post_author_id' },
     { selection: 'Post.category.id', aliasName: 'Category_id' } ]
}

Since typeorm only select the first match { selection: 'Post.category.id', aliasName: 'Post_category_id' } by using Array.find here when it builds the query, selection { selection: 'Post.category.id', aliasName: 'Category_id' } is skipped, therefore Category_id column is not returned.

So to summarize:

1. RelationIdLoader is used to find related entities
2. 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
3. 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.
4. 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.

Hope the above explanation makes sense :)

@pleerock what do you think? Can typeorm fix this by allowing same selection to be selected multiple times using different aliases so that it can generate a query like this:

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

Then the RelationIdLoader can grab the correct column to return related entities.

[icepeng]

I think it makes sense. I found out loadRelationIds in FindOption is broken when using composite key too. Seems to be related.