Inner Join query in TypeORM

Question

I have nestjs application which uses TypeORM package to interact with Postgres SQL.

I have the following code in it,

import {Entity, PrimaryGeneratedColumn, Column, CreateDateColumn, UpdateDateColumn, Index} from 'typeorm';

@Entity()
export class User {
  @PrimaryGeneratedColumn()
  public id: number;

  @Index()
  @Column({nullable: true})
  public userId: number;

  @Index()
  @Column()
  public appId: string;

  @Column({nullable: true})
  public keyId: number;

}

import {Entity, PrimaryGeneratedColumn, Column, CreateDateColumn, UpdateDateColumn, Index} from 'typeorm';

@Entity()
export class Key {
  @PrimaryGeneratedColumn()
  public id: number;

  @Index()
  @Column()
  public userId: number;

  @Index()
  @Column()
  public key: string;

}

import {Injectable} from '@nestjs/common';
import {InjectRepository} from '@nestjs/typeorm';
import {createQueryBuilder, Repository, getRepository, getManager} from 'typeorm';
import {User} from '../user.entity';
import {Key} from './key.entity';

@Injectable()
export class KeyService {
  public constructor(
    @InjectRepository(Key)
    private readonly KeyRepository: Repository<Key>,
    @InjectRepository(User)
    private readonly userRepository: Repository<User>,
  ) {}

public async fetch (userId: id): Promise <any> {
const user = await this.userRepository.findOne({where:{userId:id}});
const result = // typeORM query for inner join
return result 
}

For key and user entities, I need to create an inner Join as

SELECT * FROM key INNER JOIN user ON key.id = user."keyId" WHERE user."userId" = 1;

How to write the equivalent inner join query using typeORM?

How to execute the above SQL query using typeORM?

Answer 1

@Injectable()
export class KeyService {
  public constructor(
    @InjectRepository(Key)
    private readonly keyRepository: Repository<Key>
  ) {}

public fetch(userId: id): Promise<any> {
  return this.keyRepository.createQueryBuilder('key')
    .innerJoinAndMapOne('key.user', User, 'user', 'key.id = user.keyId')
    .where('user.userId = :userId', { userId: 1 }) // or you can change condition to 'key.userId = :userId' because of you have `userId` in Key
    .getMany(); 
}