第五章 查询

本章目标

• 掌握 FindManyOptions 的常用字段：select / where / order / skip / take
• 会用全部常用 FindOperator：Equal、Not、LessThan(OrEqual)、MoreThan(OrEqual)、Like、ILike、Between、In、IsNull、Raw、And
• 分清 where 的 AND（多字段对象）与 OR（数组）两种写法
• 会写分页查询：skip = (页码 - 1) × 每页条数
• 知道 Any 在 MySQL 下不可用、ILike 在 MySQL 下的真实 SQL
• groupBy：按字段分组查询和Having后期在讲

核心概念

find(options) 里的 options 就像一张点菜单：

• select：点哪几道菜（列）
• where：口味要求（条件）
• order：上菜顺序（排序）
• skip / take：跳过前几道、只要几道（分页）
• relations：配餐（关联加载，第 07~09 章细讲）
• withDeleted：要不要把"软删除"的也算上（第 16 章细讲）

FindManyOptions 继承自 FindOneOptions，只多了 skip / take 两个分页字段。

find 四 大类

find

import { MoreThan, ILike } from "typeorm";
import { AppDataSource } from "./data-source";
import { User } from "./entities/User";

AppDataSource.initialize()
  .then(async () => {
    const result = await AppDataSource.manager.find(User, {
      where: {
        id: MoreThan(5),
        firstName: ILike("%new_7"),
      },
    });
    console.log(JSON.stringify(result));
  })
  .catch((error) => console.log(error));

findBy

import { MoreThan, ILike } from "typeorm";
import { AppDataSource } from "./data-source";
import { User } from "./entities/User";

AppDataSource.initialize()
  .then(async () => {
    const result = await AppDataSource.manager.findBy(User, {
      id: MoreThan(5),
      firstName: ILike("%new_7"),
    });
    console.log(JSON.stringify(result));
  })
  .catch((error) => console.log(error));

findOneBy

只返回一条，找不到返回 null

import { MoreThan, ILike } from "typeorm";
import { AppDataSource } from "./data-source";
import { User } from "./entities/User";

AppDataSource.initialize()
  .then(async () => {
    const result = await AppDataSource.manager.findOneBy(User, {
      id: MoreThan(5),
    });
    console.log(JSON.stringify(result));
  })
  .catch((error) => console.log(error));

findOneByOrFail

只返回一条，找不到抛错,这样就抛错

import { MoreThan, ILike } from "typeorm";
import { AppDataSource } from "./data-source";
import { User } from "./entities/User";

AppDataSource.initialize()
  .then(async () => {
    const result = await AppDataSource.manager.findOneByOrFail(User, {
      id: MoreThan(100),
    });
    console.log(JSON.stringify(result));
  })
  .catch((error) => console.log(error));

where 的 AND 与 OR

不是数组 默认就是AND

import { MoreThan, ILike } from "typeorm";
import { AppDataSource } from "./data-source";
import { User } from "./entities/User";

AppDataSource.initialize()
  .then(async () => {
    const result = await AppDataSource.manager.find(User, {
      where: {
        id: MoreThan(5),
        firstName: ILike("%new_7"),
      },
    });
    console.log(JSON.stringify(result));
  })
  .catch((error) => console.log(error));

如果是OR 就是 条件放到一个数组里

import { MoreThan, ILike } from "typeorm";
import { AppDataSource } from "./data-source";
import { User } from "./entities/User";

AppDataSource.initialize()
  .then(async () => {
    const result = await AppDataSource.manager.find(User, {
      where: [{ id: MoreThan(5) }, { firstName: ILike("%new_7") }],
    });
    console.log(JSON.stringify(result));
  })
  .catch((error) => console.log(error));

查询关键词

从 typeorm 包导入：import { Equal, Not, LessThan, ... } from "typeorm"。

运算符	生成的 SQL	示例
Equal(v)	= v	{ age: Equal(18) }
Not(v)	!= v（包运算符则取反）	{ age: Not(18) }
LessThan(v)	< v	{ age: LessThan(18) }
LessThanOrEqual(v)	<= v	{ age: LessThanOrEqual(18) }
MoreThan(v)	> v	{ age: MoreThan(28) }
MoreThanOrEqual(v)	>= v	{ age: MoreThanOrEqual(28) }
Like("%x%")	LIKE '%x%'	{ name: Like("学生1%") }
ILike("%x%")	MySQL 下是 UPPER(col) LIKE UPPER(...)（大小写不敏感）	{ email: ILike("%STU%") }
Between(a, b)	BETWEEN a AND b（含两端）	{ age: Between(18, 22) }
In([...])	IN (...)	{ city: In(["北京", "杭州"]) }
IsNull()	IS NULL	{ email: IsNull() }
Raw(fn)	原样拼接你写的 SQL 片段	{ age: Raw((a) => \${a} % 2 = 0`) }`
And(op1, op2)	同一字段多个条件取 AND	{ age: And(MoreThan(20), LessThan(26)) }
Any([...])	= ANY(...)，Postgres 语义，MySQL 会报 SQL 语法错误，请改用 In()	—

select：只取需要的列

import { MoreThan, ILike } from "typeorm";
import { AppDataSource } from "./data-source";
import { User } from "./entities/User";

AppDataSource.initialize()
  .then(async () => {
    const result = await AppDataSource.manager.find(User, {
      select: {
        firstName: true,
      },
      where: {
        id: MoreThan(5),
      },
    });
    console.log(JSON.stringify(result));
  })
  .catch((error) => console.log(error));

order排序

• 降序 就是DESC 升序就是ASC

import { MoreThan, ILike } from "typeorm";
import { AppDataSource } from "./data-source";
import { User } from "./entities/User";

AppDataSource.initialize()
  .then(async () => {
    const result = await AppDataSource.manager.find(User, {
      select: {
        firstName: true,
        age: true,
      },
      where: {
        id: MoreThan(5),
      },
      order: {
        age: "DESC",
      },
    });
    console.log(JSON.stringify(result));
  })
  .catch((error) => console.log(error));

skip + take:分页

import { MoreThan, ILike } from "typeorm";
import { AppDataSource } from "./data-source";
import { User } from "./entities/User";

AppDataSource.initialize()
  .then(async () => {
    const page = 2;
    const pageSize = 2;
    const result = await AppDataSource.manager.find(User, {
      select: {
        firstName: true,
        age: true,
      },
      skip: (page - 1) * pageSize,
      take: pageSize,
      order: {
        age: "ASC",
      },
    });
    const total = await AppDataSource.manager.count(User);
    const totalPages = Math.ceil(total / pageSize);
    console.log(
      `当前页: ${page}, 总页数: ${totalPages}, 每页数量: ${pageSize}, 总用户数: ${total}`,
    );
    console.log(JSON.stringify(result));
  })
  .catch((error) => console.log(error));