TypeORM中的内部联接查询

Question

我有一个使用TypeORM包与Postgres SQL交互的nestjs应用程序。

我有下面的代码,

js prettyprint-override">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 
}

对于键和用户实体，我需要将内部连接创建为

选择*FROM key INNER JOIN user ON key.id=user.“keyid”，其中user.“userid”=1；

如何使用typeorm编写等效的内部联接查询？

如何使用typeorm执行上面的SQL查询？

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(); 
}