TypeORM子查询 [英] TypeORM subqueries

问题描述

基于typeORM 文档有关使用子查询的说明，如何创建子查询. 示例:

Based on typeORM docs on using subqueries, there are explained how to create subqueries. Example:

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

但是，关于SQL是什么，这是没有等价的.

But there is no equivalent as to what the SQL would be.

假设我有包含以下子查询的查询:

Supposed that I have the query which contains subqueries like the following:

SELECT a, TO_CHAR (MAX (jointable.f), 'MON YYYY') as f,
   t3.c, t3.d, t1.e
FROM table1 t1 
LEFT JOIN table2 t2 ON t2.e = t1.e
JOIN table3 t3 ON t3.d = t2.d
JOIN
 (SELECT f, t4.g, t5.e, t6.h
   FROM table4 t4 
   JOIN table5 t5 ON t4.g = t5.g
   JOIN table6 t6 ON t6.g = t4.g
   AND (t6.i = 2
        OR (t6.i = 1 AND j = 1)
       )
     WHERE t4.k = 4
     ) jointable ON t1.e = jointable.e
WHERE jointable.h = :h
AND(:d = 3 OR 
    t3."d" = :d
   )
GROUP BY a, t3.c, t3.d, t1.e
ORDER BY a ASC

对于上面的SQL查询，我应该如何使用typeORM查询生成器功能?

How should I use the typeORM query builder function for the SQL query above?

假设我创建了与上述查询中使用的所有表相关的实体.

Assuming that I had created entities related to all table being used on the query above.

推荐答案

我希望这个答案可以帮助其他人使用TypeORM子查询.

I hope this answer could help others to use TypeORM subquery.

const subquery = await getManager()
    .createQueryBuilder(table4, 't4')
    .select('"t4".f')
    .addSelect('"t4".g')
    .addSelect('"t5".e')
    .addSelect('"t6".h')
    .innerJoin(table5, 't5', '"t4".g = "t5".g')
    .innerJoin(table6, 't6', '"t6".g = "t4".g')
    .where('"t4".k = 4 AND ("t6".i = 2 OR ("t6".i = 1 AND "t6".j = 1))');

  model = await getManager()
    .createQueryBuilder(table1, 't1')
    .select('"t1".a')
    .addSelect("TO_CHAR (MAX (jointable.f), 'MON YYYY')", 'f')
    .addSelect('"t3".c')
    .addSelect('"t3".d')
    .addSelect('"t1".e')
    .leftJoin('table2', 't2', '"t2".e = "t1".e')
    .innerJoin(table3, 't3', '"t3".d = "t2".d')
    .innerJoin('('+subquery.getQuery()+')', 'jointable', '"t1".e = jointable.e')
    .where('jointable.h = :h AND (:d = 3 OR "t3".d = :d)',
      { h: h, d: d })
    .groupBy('"t1".a, "t3".c, "t3".d, "t1".e')
    .orderBy('"t1".a', 'ASC')
    .getRawMany();

我正在使用'('+subquery.getQuery()+')'来使子查询选择查询等同于

I was using '('+subquery.getQuery()+')' for getting the subquery select query as an equivalent to

(SELECT f，t4.g，t5.e，t6.h ....

(SELECT f, t4.g, t5.e, t6.h ....

......

....)可以在t1.e上连接= eableable.e

.... ) jointable ON t1.e = jointable.e

根据我的理解:

• Join实际上 .select与SQL中的select等效.您也可以添加 aliases (与SQL一样).

  .select is and equivalent of select in SQL. You could also add aliases (as in SQL).

  .addSelect与, in select

  使用选择查询可以获得两种类型的结果 builder:实体或原始结果.描述你是否想要 数据作为实体(getOne和getMany)还是其他数据 是(getRawOne和getRawMany).

  There are two types of results you can get using select query builder: entities or raw results. To describe whether you want the data as entities (getOne and getMany) or what it is(getRawOne and getRawMany).

  这篇关于TypeORM子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！