联查

联查

假设有以下实体：

import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from "typeorm";
import { Photo } from "./Photo";

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

  @Column()
  name: string;

  @OneToMany(type => Photo, photo => photo.user)
  photos: Photo[];
}
``` ```
import { Entity, PrimaryGeneratedColumn, Column, ManyToOne } from "typeorm";
import { User } from "./User";

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

  @Column()
  url: string;

  @ManyToOne(type => User, user => user.photos)
  user: User;
}

现在让我们假设你要用用户“Timber”加载他所有的 photos：

const user = await createQueryBuilder("user")
  .leftJoinAndSelect("user.photos", "photo")
  .where("user.name = :name", { name: "Timber" })
  .getOne();

你将会得到以下结果：

{
    id: 1,
    name: "Timber",
    photos: [{
        id: 1,
        url: "me-with-chakram.jpg"
    }, {
        id: 2,
        url: "me-with-trees.jpg"
    }]
}

你可以看到leftJoinAndSelect自动加载了所有 Timber 的 photos。第一个参数是你要加载的关系，第二个参数是你为此关系的表分配的别名。你可以在查询构建器中的任何位置使用此别名。例如，让我们获得所有未删除的 Timber 的 photos。

const user = await createQueryBuilder("user")
  .leftJoinAndSelect("user.photos", "photo")
  .where("user.name = :name", { name: "Timber" })
  .andWhere("photo.isRemoved = :isRemoved", { isRemoved: false })
  .getOne();

将会生成以下 SQL 查询：

SELECT user.*, photo.* FROM users user
    LEFT JOIN photos photo ON photo.user = user.id
    WHERE user.name = 'Timber' AND photo.isRemoved = FALSE

你还可以向连接表达式添加条件，而不是使用“where”：

const user = await createQueryBuilder("user")
  .leftJoinAndSelect("user.photos", "photo", "photo.isRemoved = :isRemoved", { isRemoved: false })
  .where("user.name = :name", { name: "Timber" })
  .getOne();

这将生成以下 sql 查询：

SELECT user.*, photo.* FROM users user
    LEFT JOIN photos photo ON photo.user = user.id AND photo.isRemoved = FALSE
    WHERE user.name = 'Timber'