Skip to content

Latest commit

 

History

History
977 lines (737 loc) · 24.7 KB

select-query-builder.md

File metadata and controls

977 lines (737 loc) · 24.7 KB

使用 Query Builder 查询

什么是QueryBuilder

QueryBuilder是 TypeORM 最强大的功能之一 ,它允许你使用优雅便捷的语法构建 SQL 查询,执行并获得自动转换的实体。

QueryBuilder的简单示例:

const firstUser = await connection
  .getRepository(User)
  .createQueryBuilder("user")
  .where("user.id = :id", { id: 1 })
  .getOne();

它将生成以下 SQL 查询:

SELECT
    user.id as userId,
    user.firstName as userFirstName,
    user.lastName as userLastName
FROM users user
WHERE user.id = 1

然后返回一个 User 实例:

User {
    id: 1,
    firstName: "Timber",
    lastName: "Saw"
}

如何创建和使用QueryBuilder

有几种方法可以创建Query Builder

  • 使用 connection:

    import { getConnection } from "typeorm";
    
    const user = await getConnection()
      .createQueryBuilder()
      .select("user")
      .from(User, "user")
      .where("user.id = :id", { id: 1 })
      .getOne();
  • 使用 entity manager:

    import { getManager } from "typeorm";
    
    const user = await getManager()
      .createQueryBuilder(User, "user")
      .where("user.id = :id", { id: 1 })
      .getOne();
  • 使用 repository:

    import { getRepository } from "typeorm";
    
    const user = await getRepository(User)
      .createQueryBuilder("user")
      .where("user.id = :id", { id: 1 })
      .getOne();

有 5 种不同的QueryBuilder类型可用:

  • SelectQueryBuilder - 用于构建和执行SELECT查询。 例如:

    import { getConnection } from "typeorm";
    
    const user = await getConnection()
      .createQueryBuilder()
      .select("user")
      .from(User, "user")
      .where("user.id = :id", { id: 1 })
      .getOne();
  • InsertQueryBuilder - 用于构建和执行INSERT查询。 例如:

    import { getConnection } from "typeorm";
    
    await getConnection()
      .createQueryBuilder()
      .insert()
      .into(User)
      .values([{ firstName: "Timber", lastName: "Saw" }, { firstName: "Phantom", lastName: "Lancer" }])
      .execute();
  • UpdateQueryBuilder - 用于构建和执行UPDATE查询。 例如:

    import { getConnection } from "typeorm";
    
    await getConnection()
      .createQueryBuilder()
      .update(User)
      .set({ firstName: "Timber", lastName: "Saw" })
      .where("id = :id", { id: 1 })
      .execute();
  • DeleteQueryBuilder - 用于构建和执行DELETE查询。 例如:

    import { getConnection } from "typeorm";
    
    await getConnection()
      .createQueryBuilder()
      .delete()
      .from(User)
      .where("id = :id", { id: 1 })
      .execute();
  • RelationQueryBuilder - 用于构建和执行特定于关系的操作[TBD]。

你可以在其中切换任何不同类型的查询构建器,一旦执行,则将获得一个新的查询构建器实例(与所有其他方法不同)。

使用QueryBuilder获取值

要从数据库中获取单个结果,例如通过 id 或 name 获取用户,必须使用getOne

const timber = await getRepository(User)
  .createQueryBuilder("user")
  .where("user.id = :id OR user.name = :name", { id: 1, name: "Timber" })
  .getOne();

要从数据库中获取多个结果,例如,要从数据库中获取所有用户,请使用getMany

const users = await getRepository(User)
  .createQueryBuilder("user")
  .getMany();

使用查询构建器查询可以获得两种类型的结果:entitiesraw results。 大多数情况下,你只需要从数据库中选择真实实体,例如 users。 为此,你可以使用getOnegetMany。 但有时你需要选择一些特定的数据,比方说所有sum of all user photos。 此数据不是实体,它称为原始数据。 要获取原始数据,请使用getRawOnegetRawMany。 例如:

const { sum } = await getRepository(User)
  .createQueryBuilder("user")
  .select("SUM(user.photosCount)", "sum")
  .where("user.id = :id", { id: 1 })
  .getRawOne();
const photosSums = await getRepository(User)
  .createQueryBuilder("user")
  .select("user.id")
  .addSelect("SUM(user.photosCount)", "sum")
  .where("user.id = :id", { id: 1 })
  .getRawMany();

// 结果会像这样: [{ id: 1, sum: 25 }, { id: 2, sum: 13 }, ...]

什么是别名?

我们使用createQueryBuilder("user")。 但什么是"user"? 它只是一个常规的 SQL 别名。 我们在任何地方都使用别名,除非我们处理选定的数据。

createQueryBuilder("user") 相当于:

createQueryBuilder()
  .select("user")
  .from(User, "user");

这会生成以下 sql 查询:

SELECT ... FROM users user

在这个 SQL 查询中,users是表名,user是我们分配给该表的别名。

稍后我们使用此别名来访问表:

createQueryBuilder()
  .select("user")
  .from(User, "user")
  .where("user.name = :name", { name: "Timber" });

以上代码会生成如下 SQL 语句:

SELECT ... FROM users user WHERE user.name = 'Timber'

看到了吧,我们使用了在创建查询构建器时分配的user别名来使用 users 表。

一个查询构建器不限于一个别名,它们可以有多个别名。 每个选择都可以有自己的别名,你可以选择多个有自己别名的表,你可以使用自己的别名连接多个表。 你也可以使用这些别名来访问选择的表(或正在选择的数据)。

使用参数来转义数据

我们使用了where("user.name = :name", { name: "Timber" }). {name:“Timber”}代表什么? 这是我们用来阻止 SQL 注入的参数。 我们可以写:where("user.name ='"+ name +"'),但是这不安全,因为有可能被 SQL 注入。 安全的方法是使用这种特殊的语法:where("user.name =name",{name:"Timber"}),其中name是参数名,值在对象中指定: {name:"Timber"}

.where("user.name = :name", { name: "Timber" })

是下面的简写:

.where("user.name = :name")
.setParameter("name", "Timber")

注意:不要在查询构建器中为不同的值使用相同的参数名称。如果多次设置则后值将会把前面的覆盖。

还可以提供一组值,并使用特殊的扩展语法将它们转换为SQL语句中的值列表:

.where("user.name IN (:...names)", { names: [ "Timber", "Cristal", "Lina" ] })

该语句将生成:

WHERE user.name IN ('Timber', 'Cristal', 'Lina')

添加WHERE表达式

添加 WHERE 表达式就像:

createQueryBuilder("user").where("user.name = :name", { name: "Timber" });

将会生成以下 SQL 语句:

SELECT ... FROM users user WHERE user.name = 'Timber'

你可以将 AND 添加到现有的 WHERE 表达式中:

createQueryBuilder("user")
  .where("user.firstName = :firstName", { firstName: "Timber" })
  .andWhere("user.lastName = :lastName", { lastName: "Saw" });

将会生成以下 SQL 语句:

SELECT ... FROM users user WHERE user.firstName = 'Timber' AND user.lastName = 'Saw'

你也可以添加 OR 添加到现有的 WHERE 表达式中:

createQueryBuilder("user")
  .where("user.firstName = :firstName", { firstName: "Timber" })
  .orWhere("user.lastName = :lastName", { lastName: "Saw" });

将会生成以下 SQL 语句:

SELECT ... FROM users user WHERE user.firstName = 'Timber' OR user.lastName = 'Saw'

你可以使用Brackets将复杂的WHERE表达式添加到现有的WHERE中:

createQueryBuilder("user")
    .where("user.registered = :registered", { registered: true })
    .andWhere(new Brackets(qb => {
        qb.where("user.firstName = :firstName", { firstName: "Timber" })
          .orWhere("user.lastName = :lastName", { lastName: "Saw" })

将会生成以下 SQL 语句:

SELECT ... FROM users user WHERE user.registered = true AND (user.firstName = 'Timber' OR user.lastName = 'Saw')

你可以根据需要组合尽可能多的ANDOR表达式。 如果你多次使用.where,你将覆盖所有以前的WHERE表达式。 注意:小心orWhere - 如果你使用带有ANDOR表达式的复杂表达式,请记住他们将无限制的叠加。 有时你只需要创建一个 where 字符串,避免使用orWhere

添加HAVING表达式

添加HAVING表达式很简单:

createQueryBuilder("user").having("user.name = :name", { name: "Timber" });

将会生成以下 SQL 语句:

SELECT ... FROM users user HAVING user.name = 'Timber'

你可以添加 AND 到已经存在的 HAVING 表达式中:

createQueryBuilder("user")
  .having("user.firstName = :firstName", { firstName: "Timber" })
  .andHaving("user.lastName = :lastName", { lastName: "Saw" });

将会生成以下 SQL 语句:

SELECT ... FROM users user HAVING user.firstName = 'Timber' AND user.lastName = 'Saw'

你可以添加 OR 到已经存在的 HAVING 表达式中:

createQueryBuilder("user")
  .having("user.firstName = :firstName", { firstName: "Timber" })
  .orHaving("user.lastName = :lastName", { lastName: "Saw" });

将会生成以下 SQL 语句:

SELECT ... FROM users user HAVING user.firstName = 'Timber' OR user.lastName = 'Saw'

你可以根据需要组合尽可能多的ANDOR表达式。 如果使用多个.having,后面的将覆盖所有之前的HAVING表达式。

添加ORDER BY表达式

添加 ORDER BY 很简单:

createQueryBuilder("user").orderBy("user.id");

将会生成一下 SQL 语句:

SELECT ... FROM users user ORDER BY user.id

你可以将排序方向从升序更改为降序(或反之亦然):

createQueryBuilder("user").orderBy("user.id", "DESC");

createQueryBuilder("user").orderBy("user.id", "ASC");

也可以添加多个排序条件:

createQueryBuilder("user")
  .orderBy("user.name")
  .addOrderBy("user.id");

还可以使用排序字段作为一个 map:

createQueryBuilder("user").orderBy({
  "user.name": "ASC",
  "user.id": "DESC"
});

如果你使用了多个.orderBy,后面的将覆盖所有之前的ORDER BY表达式。

添加GROUP BY表达式

添加 GROUP BY 表达式很简单:

createQueryBuilder("user").groupBy("user.id");

将会生成以下 SQL 语句:

SELECT ... FROM users user GROUP BY user.id

如果要使用更多 group-by, 则可以使用 addGroupBy:

createQueryBuilder("user")
  .groupBy("user.name")
  .addGroupBy("user.id");

如果使用了多个.groupBy ,则后面的将会覆盖之前所有的 ORDER BY 表达式。

添加LIMIT表达式

添加 LIMIT 表达式很简单:

createQueryBuilder("user").limit(10);

将会生成以下 SQL 语句:

SELECT ... FROM users user LIMIT 10

生成的 SQL 查询取决于数据库的类型(SQL,mySQL,Postgres 等)。 注意:如果你使用带有连接或子查询的复杂查询,LIMIT 可能无法正常工作。 如果使用分页,建议使用take代替。

添加OFFSET表达式

添加 SQLOFFSET表达式很简单:

createQueryBuilder("user").offset(10);

将会生成以下 SQL 语句:

SELECT ... FROM users user OFFSET 10

生成的 SQL 查询取决于数据库的类型(SQL,mySQL,Postgres 等)。 注意:如果你使用带有连接或子查询的复杂查询,OFFSET 可能无法正常工作。 如果使用分页,建议使用skip代替。

联查

假设有以下实体:

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'

内联和左联

如果你想使用INNER JOIN而不是LEFT JOIN,只需使用innerJoinAndSelect

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

This will generate:

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

LEFT JOININNER JOIN之间的区别在于,如果没有任何 photos,INNER JOIN将不会返回 user。 即使没有 photos,LEFT JOIN也会返回 user。 要了解有关不同连接类型的更多信息,请参阅 SQL 文档.

不使用条件的联查

你可以在不使用条件的情况下联查数据。 要做到这一点,使用leftJoininnerJoin

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

将会生成如下 SQL 语句:

SELECT user.* FROM users user
    INNER JOIN photos photo ON photo.user = user.id
    WHERE user.name = 'Timber'

这将会返回 Timber 如果他有 photos,但是并不会返回他的 photos。

联查任何实体或表

你不仅能联查关系,还能联查任何其他实体或表。

例如:

const user = await createQueryBuilder("user")
  .leftJoinAndSelect(Photo, "photo", "photo.userId = user.id")
  .getMany();
const user = await createQueryBuilder("user")
  .leftJoinAndSelect("photos", "photo", "photo.userId = user.id")
  .getMany();

联查和映射功能

profilePhoto添加到User实体,你可以使用QueryBuilder将任何数据映射到该属性:

export class User {
  /// ...
  profilePhoto: Photo;
}
const user = await createQueryBuilder("user")
  .leftJoinAndMapOne("user.profilePhoto", "user.photos", "photo", "photo.isForProfile = TRUE")
  .where("user.name = :name", { name: "Timber" })
  .getOne();

这将加载 Timber 的个人资料照片并将其设置为user.profilePhoto。 如果要加载并映射单个实体,请使用leftJoinAndMapOne。 如果要加载和映射多个实体,请使用leftJoinAndMapMany

获取生成的sql查询语句

有时你可能想要获取QueryBuilder生成的 SQL 查询。 为此,请使用getSql

const sql = createQueryBuilder("user")
  .where("user.firstName = :firstName", { firstName: "Timber" })
  .orWhere("user.lastName = :lastName", { lastName: "Saw" })
  .getSql();

出于调试目的,你也可以使用printSql

const users = await createQueryBuilder("user")
  .where("user.firstName = :firstName", { firstName: "Timber" })
  .orWhere("user.lastName = :lastName", { lastName: "Saw" })
  .printSql()
  .getMany();

此查询将返回 users 并将使用的 sql 语句打印到控制台。

获得原始结果

使用选择查询构建器可以获得两种类型的结果:entitiesraw results。 大多数情况下,你只需要从数据库中选择真实实体,例如 users。 为此,你可以使用getOnegetMany。 但是,有时需要选择特定数据,例如 sum of all user photos。 这些数据不是实体,它被称为原始数据。 要获取原始数据,请使用getRawOnegetRawMany。 例如:

const { sum } = await getRepository(User)
  .createQueryBuilder("user")
  .select("SUM(user.photosCount)", "sum")
  .where("user.id = :id", { id: 1 })
  .getRawOne();
const photosSums = await getRepository(User)
  .createQueryBuilder("user")
  .select("user.id")
  .addSelect("SUM(user.photosCount)", "sum")
  .where("user.id = :id", { id: 1 })
  .getRawMany();

// 结果将会像这样: [{ id: 1, sum: 25 }, { id: 2, sum: 13 }, ...]

流数据

你可以使用stream来返回流。 Streaming 返回原始数据,必须手动处理实体转换:

const stream = await getRepository(User)
  .createQueryBuilder("user")
  .where("user.id = :id", { id: 1 })
  .stream();

使用分页

大多数情况下,在开发应用程序时,你可能需要分页功能。 如果你的应用程序中有分页,page slider 或无限滚动组件,则使用此选项。

const users = await getRepository(User)
  .createQueryBuilder("user")
  .leftJoinAndSelect("user.photos", "photo")
  .take(10)
  .getMany();

将会返回前 10 个 user 的 photos。

const users = await getRepository(User)
  .createQueryBuilder("user")
  .leftJoinAndSelect("user.photos", "photo")
  .skip(10)
  .getMany();

将返回除了前 10 个 user 以外的所有 user 的 photos。

你可以组合这些方法:

const users = await getRepository(User)
  .createQueryBuilder("user")
  .leftJoinAndSelect("user.photos", "photo")
  .skip(5)
  .take(10)
  .getMany();

这将跳过前 5 个 users,并获取他们之后的 10 个 user。

takeskip可能看起来像我们正在使用limitoffset,但它们不是。 一旦你有更复杂的连接或子查询查询,limitoffset可能无法正常工作。 使用takeskip可以防止这些问题。

加锁

QueryBuilder 支持 optimistic 和 pessimistic 锁定。 要使用 pessimistic 读锁定,请使用以下方式:

const users = await getRepository(User)
  .createQueryBuilder("user")
  .setLock("pessimistic_read")
  .getMany();

要使用 pessimistic 写锁定,请使用以下方式:

const users = await getRepository(User)
  .createQueryBuilder("user")
  .setLock("pessimistic_write")
  .getMany();

要使用 optimistic 读锁定,请使用以下方式:

const users = await getRepository(User)
  .createQueryBuilder("user")
  .setLock("optimistic", existUser.version)
  .getMany();

要使用 dirty 读锁定,请使用以下方式:

const users = await getRepository(User)
    .createQueryBuilder("user")
    .setLock("dirty_read")
    .getMany();

Optimistic 锁定与@Version@UpdatedDate装饰器一起使用。

查询部分字段

如果只想选择实体的某些属性,可以使用以下语法:

const users = await getRepository(User)
  .createQueryBuilder("user")
  .select(["user.id", "user.name"])
  .getMany();

这只会选择Useridname

使用子查询

你可以轻松创建子查询。 FROMWHEREJOIN表达式都支持子查询。 例如:

const qb = await getRepository(Post).createQueryBuilder("post");
const posts = qb
  .where(
    "post.title IN " +
      qb
        .subQuery()
        .select("user.name")
        .from(User, "user")
        .where("user.registered = :registered")
        .getQuery()
  )
  .setParameter("registered", true)
  .getMany();

使用更优雅的方式来做同样的事情:

const posts = await connection
  .getRepository(Post)
  .createQueryBuilder("post")
  .where(qb => {
    const subQuery = qb
      .subQuery()
      .select("user.name")
      .from(User, "user")
      .where("user.registered = :registered")
      .getQuery();
    return "post.title IN " + subQuery;
  })
  .setParameter("registered", true)
  .getMany();

或者,你可以创建单独的查询构建器并使用其生成的 SQL:

const userQb = await connection
  .getRepository(User)
  .createQueryBuilder("user")
  .select("user.name")
  .where("user.registered = :registered", { registered: true });

const posts = await connection
  .getRepository(Post)
  .createQueryBuilder("post")
  .where("post.title IN (" + userQb.getQuery() + ")")
  .setParameters(userQb.getParameters())
  .getMany();

你可以在FROM中创建子查询,如下所示:

const userQb = await connection
  .getRepository(User)
  .createQueryBuilder("user")
  .select("user.name", "name")
  .where("user.registered = :registered", { registered: true });

const posts = await connection
  .createQueryBuilder()
  .select("user.name", "name")
  .from("(" + userQb.getQuery() + ")", "user")
  .setParameters(userQb.getParameters())
  .getRawMany();

或使用更优雅的语法:

const posts = await connection
  .createQueryBuilder()
  .select("user.name", "name")
  .from(subQuery => {
    return subQuery
      .select("user.name", "name")
      .from(User, "user")
      .where("user.registered = :registered", { registered: true });
  }, "user")
  .getRawMany();

如果想添加一个子查询做为"second from",请使用addFrom

你也可以在SELECT语句中使用子查询:

const posts = await connection
  .createQueryBuilder()
  .select("post.id", "id")
  .addSelect(subQuery => {
    return subQuery
      .select("user.name", "name")
      .from(User, "user")
      .limit(1);
  }, "name")
  .from(Post, "post")
  .getRawMany();

隐藏列

如果要查询的模型具有"select:false"的列,则必须使用addSelect函数来从列中检索信息。

假设你有以下实体:

import { Entity, PrimaryGeneratedColumn, Column } from "typeorm";

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

  @Column()
  name: string;

  @Column({ select: false })
  password: string;
}

使用标准的find或查询,你将不会接收到模型的password属性。 但是,如果执行以下操作:

const users = await connection
  .getRepository(User)
  .createQueryBuilder()
  .select("user.id", "id")
  .addSelect("user.password")
  .getMany();

你将在查询中获得属性password