Self referencing / tree entities don't work

[josephktcheung]

Self referencing entities don't work at the moment. Here's reproduction branch https://github.com/josephktcheung/typescript-advanced-example/tree/feature/self_referencing.

Here's my category with tree structure (adjacency list):

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

@Entity()
export class Category {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @ManyToMany(() => Post, post => post.categories)
    posts: Post[];

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

    @OneToMany(() => Category, category => category.parent)
    children: Category[];
}

Here's how I add child category to parent category in controller:

import { Controller, Mutation, Query } from "vesper";
import { EntityManager } from "typeorm";
import { Category } from "../entity/Category";
import { CategorySaveArgs } from "../args/CategorySaveArgs";
import { AddCategoryChildArgs } from '../args/AddCategoryChildArgs';

@Controller()
export class CategoryController {

    constructor(private entityManager: EntityManager) {
    }

    @Query()
    categories(): Promise<Category[]> {
        return this.entityManager.find(Category);
    }

    @Query()
    category({ id }: { id: number }): Promise<Category> {
        return this.entityManager.findOne(Category, id);
    }

    @Mutation()
    async categorySave(args: CategorySaveArgs): Promise<Category> {
        const category = args.id ? await this.entityManager.findOneOrFail(Category, args.id) : new Category();
        category.name = args.name;
        return this.entityManager.save(category);
    }

    @Mutation()
    async addCategoryChild(args: AddCategoryChildArgs): Promise<Category> {
        const parent = await this.entityManager.findOneOrFail(Category, args.parentId);
        const child = await this.entityManager.findOneOrFail(Category, args.childId);
        child.parent = parent;

        return this.entityManager.save(child);
    }
}

Here's my category graphql:

type Category {
    id: Int
    name: String
    posts: [Post]
    parent: Category
    children: [Category]
}

Create 2 categories, assign child to parent using the following mutation:

mutation {
  addCategoryChild(parentId: 1, childId: 2) {
    id
    name
    parent {
      id
      name
    }
    children {
      id
      name
    }
  }
}

If I query categories like this:

{
  categories {
    id
    name
    parent {
      id
      name
    }
    children {
      id
      name
    }
  }
}

The result is:

{
  "data": {
    "categories": [
      {
        "id": 1,
        "name": "category #1",
        "parent": {
          "id": 1,
          "name": "category #1"
        },
        "children": []
      },
      {
        "id": 2,
        "name": "category #2",
        "parent": null,
        "children": []
      }
    ]
  }
}

You can see that category id 1's parent is itself and it has no children, while category id 2 has no parent. But if you look at the category table in sqlite the relationship is set up correctly:

I believe there's something wrong with typeorm's RelationIdLoader that it cannot handle self referencing entities correctly. For example, this is one of the sql queries typeorm generates when the categories query is run:

query: SELECT "Category"."id" AS "Category_id", "Category"."parentId" AS "Category_id" FROM "category" "Category" WHERE "Category"."parentId" IN (?, ?) -- PARAMETERS: [1,2]

You can see that when selecting primary column and join column ids, the join column id is selected as "Category_id" which is the same as the primary column alias.

[josephktcheung]

To work around this issue, I created a custom resolver to resolve children:

  @Resolve()
  public async children(categories: Category[]) {
    const categoryIds = categories.map(category => category.id);

    const children = await this.entityManager
      .createQueryBuilder(Category, 'category')
      .where('category."parentId" IN (:...ids)', { ids: categoryIds })
      .getMany();

    return categories.map(category =>
      children.filter(child => child.parentId === category.id),
    );
  }

[pleerock]

Fix was released in 0.2.4 please check it.

[josephktcheung]

Checked. It's fixed. Thanks @pleerock !