node.js - TypeORM 中的内连接查询

Question

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

我有以下代码，

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 
}

对于键和用户实体，我需要创建一个内部联接作为

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

如何使用 typeORM 编写等效的内连接查询？

如何使用 typeORM 执行上述 SQL 查询？

标签： node.jstypescriptpostgresqlnestjstypeorm

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