使用子查询

使用子查询

你可以轻松创建子查询。 FROM，WHERE和JOIN表达式都支持子查询。例如：

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