版本：0.17.0+

database

typeorm join table
✅ join using id(string) other than Entity
❌ join return flatted list other than nested
typeorm not null constraint failed
PASS: better-sqlite3 NODE_MODULE_VERSION mismatch | rebuild failed
PASS: better-sqlite3 ReferenceError: better_sqlite3_1 is not defined, both jest and mocha
mocha + ts + esm

typeorm join table

✅ join using id(string) other than Entity

I have checked a lot of typeorm documentation and relative discussion, but to find they all use an extra column to specify the table to join.

For example, int the case below, the key of user in Profile refers to User, and the key of profile in User refers to Profile.

# Entity Profile
import {Entity, PrimaryGeneratedColumn, Column, OneToOne} from "typeorm";
import {User} from "./User";

@Entity()
export class Profile {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    gender: string;

    @Column()
    photo: string;

    @OneToOne(() => User, user => user.profile) // specify inverse side as a second parameter
    user: User;

}

# Entity User
import {Entity, PrimaryGeneratedColumn, Column, OneToOne, JoinColumn} from "typeorm";
import {Profile} from "./Profile";

@Entity()
export class User {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @OneToOne(() => Profile, profile => profile.user) // specify inverse side as a second parameter
    @JoinColumn()
    profile: Profile;

}

However, in my own business scene, since I have two tables with the equal level and the same id property, it's redundant for me to specify an extra column named as erpId or trdId or foreignId or anything.

Besides, since the trd and the erp are saved into db not synchronously, the example listed on the documentation won't help to us.

Through my hard experiment and observation, I finally realized the join key is some kind of id (not real object) which means if only I specified the correct id, the join action then would be executed successfully.

That's it! Since we have already know the id of item to be join, which is directly the id of the item itself, then we can just let the id to be the joined key, like this:

# Entity Erp
import { Column, Entity, OneToOne, PrimaryColumn } from 'typeorm';
import { TrdModel } from './trd';

@Entity()
export class ErpModel {

  @OneToOne(() => TrdModel, trd => trd.id)
  @PrimaryColumn()
  id: string;

}

# Entity Trd
import { Column, Entity, JoinColumn, OneToOne, PrimaryColumn } from 'typeorm';
import { ErpModel } from './erp';

@Entity()
export class TrdModel  {
  @OneToOne(() => ErpModel, erp=>erp.id )
  @JoinColumn()
  @PrimaryColumn()
  id: string;
}

So I can use the following query to get a joined table:

// src/main/modules/queryDB/db.ts:40
// map erp into `id`, so `id` would go away
      getConnection()
        .leftJoinAndSelect('trd.id', 'erp')
        .skip(skip)
        .limit(limit)
        .getMany()
    );

// map erp into `erp`, so `id` still existed
      getConnection()
        .manager.createQueryBuilder(TrdModel, 'trd')
        .leftJoinAndMapOne('trd.erp', ErpModel, 'erp', 'erp.id = trd.id')
        .skip(skip)
        .limit(limit)
        .getMany()
    );