Search code examples
mysqlnode.jstypeorm

How to use innerJoinAndSelect to merge two tables into one?

everybody! I have two related tables, and one of them contains column with id of the second table's record. So I want to extract several properties from the second table and сoncatenate them with the result of the first one.

I faced the problem which is when I try to make a query via SQL string(that is SELECT * FROM `characters` `c` INNER JOIN `money` `m` ON `c`.`bank` = `m`.`id` WHERE uuid = ?) — everything works fine. But when I do the same things via TypeORM the queryBuilder returns only data from the first table (characters).

My chatacters Entity:

export default class Character extends BaseEntity {
  @PrimaryGeneratedColumn()
  public id: number;

  @Column()
  uuid: number;

  ...

  @OneToOne(() => Money)
  @JoinColumn()
  bank: Money;

  ...

  public static async findByUuid(uuid: number): Promise<Character> {
    return await getManager()
      .createQueryBuilder(Character, "c")
      .innerJoinAndSelect(Money, "m", "c.bank = m.id")
      .where("uuid = :uuid ", { uuid })
      .getOne();
  }
}

My money Entity:

export default class Money extends BaseEntity {
  @PrimaryGeneratedColumn()
   public id: number;

  @Column()
  type: number;

  @Column()
  holder: string;

  @Column()
  balance: number;
}

I tried to replace c.bank = m.id with c.bankId = m.id, but it also didn't work. I tried to remove @OneToOne relation, but I got the same effect. I turned "logging" option on and copied SQL string generated, then I executed it via PhpMyAdmin and ... It worked! It seems TypeORM modifies query's result and gives me the row of the first table only. I don't understand what goes wrong. Please, help.

Solution

  • You should be able to do this in two ways:

    1. Using QueryBuilder (like you tried):

    const character = await getManager()
      .createQueryBuilder(Character, "c")
      .innerJoinAndSelect("c.bank", "bank")
      .where("c.uuid = :uuid ", { uuid })
      .getOne();
return character;

    2. Using findOne:

    const character = await getManager()
      .getRepository(Character)
      .findOne({ relations: ["bank"], where: { uuid } });
return character;

    Now you should be able to access bank using character.bank

    Update: For removing the foreign key bankId from characters table, you can do something like below.

    @OneToOne(() => Money, { createForeignKeyConstraints: false })
bank: Money;

    See the reference from typeorm documentation.

    As you said, if some other server is trying to save id into bank field, that would be problematic because your Character entity's bank property is of type Money. So better if you change the name of the relation to something else to avoid conflict.

    Hope this helps. Cheers 🍻 !!!