Common Table Expressions

[sylvainlap]

Hello,

This is more a question than an issue, but how can I build a CTE (with) with TypeORM ? Is there a built in feature to do this, or should I use the raw sql query builder ?

Thanks.

[pleerock]

Hi, no, there is no, you probably need to execute raw sql query using query method. But if orm would have such functionality, how do you see it there?

[sylvainlap]

Knex, for example, allows to build CTE thanks to .with(): http://knexjs.org/#Builder-with

[pleerock]

Sorry but I can't imagine how this can be integrated into typeorm's query builder semantics. TypeORM's QueryBuilder purpose is to select some model and return it. Can you create a feature proposal on how this can be implemented in our QueryBuilder and how it will work.

[pleerock]

@sylvainlap did you have a chance to take a look on this issue deeply?

[sylvainlap]

Actually, it was more a question than a feature request. I can close the issue.

[jtmthf]

I have a use case where a with CTE would be useful and I think it could work with the existing API. What I'm trying to do is get a list of items by ids and return the results in the order of the ids. The following raw query works with postgres 9.6+.

with x (id_list) as (
  values (array[42,48,43])
)
select c.*
from comments c, x
where id = any (x.id_list)
order by array_position(x.id_list, c.id);

However I'd ideally want to avoid using raw queries due to the lack of type-safety and the difficulty in mapping the results back to entities. What I'd like to do is to use an API similar to below.

const comments = await getRepository(Comment)
    .createQueryBuilder('comment')
    .with('x(id_list)', 'values(:ids)')
    .addFrom('x', 'x')
    .where('comment.id = any(x.id_list)')
    .orderBy('array_position(x.id_list, comment.id)')
    .setParameter('ids', [42, 48, 43])
    .getMany();

Understandably this is also possible with the following simpler query builder expression:

const ids = [42, 48, 43];
const comments = await getRepository(Comment)
    .createQueryBuilder('comment')
    .whereInIds(ids)
    .orderBy('array_position(:ids, comment.id)')
    .setParameter({ids})
    .getMany()

However I'd like not to send the array of ids twice especially if there is a large number of them. A possible API for with could be similar to Knex and be with(alias: string, query: ((qb: this) => string) | string): this. Some other examples from Knex would be written as:

Knex

knex.with('with_alias', knex.raw('select * from "books" where "author" = ?', 'Test')).select('*').from('with_alias')

TypeORM

const books = await getRepository(Book)
    .createQueryBuilder()
    .with('with_alias', 'select * from "books" where "author" = :author')
    .select()
    .from('with_alias')
    .setParameter('author', 'Test')
    .getMany();

Knex

knex.with('with_alias', (qb) => {
  qb.select('*').from('books').where('author', 'Test')
}).select('*').from('with_alias') 

TypeORM

const books = await getRepository(Book)
    .createQueryBuilder()
    .with('with_alias', qb => qb.select().from('books').where('author', 'Test').getQuery())
    .select()
    .from('with_alias')
    .getMany();

[Neo-Zhixing]

I know it's a bit hacky but here's a workaround:

const originalQuery = qb.getQuery
const originalParams = qb.getParameters
qb.getQuery = () => {
  const a = originalQuery.call(qb)
  return `WITH lb AS (${a}) SELECT * FROM lb WHERE abs(rank - (${rankQuery})) <= 3`
}
qb.getParameters = () => {
  const a = originalParams.call(qb)
  a.user = user
  return a
}

[sloan-dog]

@AlexMesser @pleerock What is the status of this issue and is there any assistance I can provide (implmentation) ? You could say I have a vested interest.

[jtmthf]

@sloan-dog if you like the API I proposed, I can get it implemented this weekend. I've been digging around a lot through the query-builder lately to implement a different feature so this shouldn't be too hard to do.

[tvoloshyn]

@jtmthf Will the proposed API support WITH RECURSIVE as well ?

[jtmthf]

@tvoloshyn I'm not sure if it's the best way, but I can implement it the same way as Knex's API as .withRecursive(alias, function|raw) where withRecursive operates identically to with, but appends RECURSIVE after WITH.

[jtmthf]

After further review, I don't think that syntax will work for recursive CTE's as it doesn't support parameters. That feature may need to be a separate discussion and PR.

[ejose19]

@jtmthf Do you have any work on this feature I can contribute? Your proposed api seems very useful and way better than dealing with raw queries.

[ejose19]

@jtmthf Do you have any work on this feature I can contribute? Your proposed api seems very useful and way better than dealing with raw queries.

[jtmthf]

@ejose19 I still need to get around to working on this. I've been spending more time lately adding keyset pagination support as implemented by JOOQ

[fullofcaffeine]

I know it's a bit hacky but here's a workaround:

const originalQuery = qb.getQuery
const originalParams = qb.getParameters
qb.getQuery = () => {
  const a = originalQuery.call(qb)
  return `WITH lb AS (${a}) SELECT * FROM lb WHERE abs(rank - (${rankQuery})) <= 3`
}
qb.getParameters = () => {
  const a = originalParams.call(qb)
  a.user = user
  return a
}

Thanks a bunch for sharing the workaround! I managed to succesfully use your technique to implement the recursive tree querying described here: https://schinckel.net/2014/09/13/long-live-adjacency-lists/.

[ameinhardt]

I tried to overwrite a queryBuilders getQueryAndParameters() and also its clone(). If the CTE has parameter, it also needs a way of rewriting param (and nativeParameters) placeholder. I guess at least for cockroachdb, postgres ($...) and mssql (@...).
Eventually, paging with relations still doesn't work, because it uses a more complex mechanism.
At least the workaround works for simple cases.

[ahsan-babar]

I know it's a bit hacky but here's a workaround:

const originalQuery = qb.getQuery
const originalParams = qb.getParameters
qb.getQuery = () => {
  const a = originalQuery.call(qb)
  return `WITH lb AS (${a}) SELECT * FROM lb WHERE abs(rank - (${rankQuery})) <= 3`
}
qb.getParameters = () => {
  const a = originalParams.call(qb)
  a.user = user
  return a
}

@Neo-Zhixing Could you please describe how can I use this snippet for WITH clause of Query Builder?

[dustin-rcg]

Can we have a querybuilder that takes at a top level, any WITHs and then an array of UNION or UNION ALL subqueries? That can be a SuperSelectQueryBuilder that has like with, addWith, union, addUnion or similar. Then redesign SelecQueryBuilder to take a scopeIndex parameter, such that the param names become (in mssql for example) @<scopeIndex>_<paramIndex>. Then we have a model that includes a way to combine multiple queries and their parameters as UNION or UNION ALL, plus the WITHs. Having SelectQueryBuilder not aware that its parameters need to be scoped is too limiting. Thanks.

For that matter, scopes could be recursive, if necessary. So SelectQueryBuilder takes an array of scope indexes when it produces the actual SQL and parameters. This would be great, since it would mean that params in subqueries wouldn't have to be registered manually in the parent query. Just use nested scopes to produce the actual parameter names. Visitor pattern.

[dustin-rcg]

Here's an implementation I have working. Subclassing SelectQueryBuilder works fine it seems.

import { Connection, QueryRunner, SelectQueryBuilder } from "typeorm";

export class CteSelectQueryBuilder<T> extends SelectQueryBuilder<T> {
  protected commonTableExprs: Record<string, string> = {};

  constructor(connection: Connection, queryRunner?: QueryRunner) {
    super(connection, queryRunner);
  }

  addCommonTableExpr(
    name: string,
    expr: string | ((qb: SelectQueryBuilder<any>) => SelectQueryBuilder<any>)
  ) {
    if (typeof expr == "function") {
      const subQueryBuilder = expr(this.subQuery());
      this.setParameters(subQueryBuilder.getParameters());
      this.commonTableExprs[name] = subQueryBuilder.getQuery();
    } else {
      this.commonTableExprs[name] = expr;
    }
  }

  getQuery() {
    const ctes = Object.entries(this.commonTableExprs);
    if (ctes.length) {
      return [
        "WITH",
        ctes.map(([name, expr]) => `${name} AS ${expr}`).join(", "),
        super.getQuery(),
      ].join(" ");
    }
    return super.getQuery();
  }
}

[timfrans]

@Ginden
Are there any plans to make this available for 0.2.x versions?

[Ginden]

@timfrans No.

[mopcweb]

@dustin-rcg thx so much for your approach.

To make it work properly with native TypeORM's queryBuilder for all methods (which may call getQuery deeply nested) i found out that it is necessary to patch it. Also, in order to make commonTableExpressions be encapsulated separately for each specific query, it is necessary to patch QueryExpressionMap.

Note Since typeorm@0.3.3 it is already implemented ability to use CTE, still for other versions this will be useful.

So the solution which works for me for all native methods such as getManyAndCount etc:

// patchTypeOrmQueryBuilderAddCommonTableExpressionsSupport.ts
import { SelectQueryBuilder, UpdateQueryBuilder, DeleteQueryBuilder, InsertQueryBuilder, RelationQueryBuilder } from 'typeorm';
import { QueryExpressionMap } from 'typeorm/query-builder/QueryExpressionMap';

export function patchTypeOrmQueryBuilderAddCommonTableExpressionsSupport(
  queryBuilder: OptionableQueryBuilder = require('typeorm').SelectQueryBuilder,
): void {
  const queryExpressionMap: typeof QueryExpressionMap = require('typeorm/query-builder/QueryExpressionMap').QueryExpressionMap;
  const queryExpressionMapPrototype = queryExpressionMap.prototype;
  const originalClone = queryExpressionMapPrototype.clone;

  const qbPrototype = queryBuilder.prototype;
  const originalGetQuery = qbPrototype.getQuery;

  Object.defineProperty(queryExpressionMapPrototype, 'clone', {
    value: function clone() {
      const context = this as QueryExpressionMap; // For typization only
      const result = originalClone.call(context);
      result.commonTableExpressions = { ...context.commonTableExpressions };
      return result;
    },
  });

  Object.defineProperty(qbPrototype, 'getQuery', {
    value: function getQuery(): string {
      const context = this as SelectQueryBuilder<unknown>; // For typization only
      const ctes = Object.entries(context.expressionMap.commonTableExpressions ?? { });

      if (ctes.length && !context.expressionMap.subQuery) {
        return [
          'WITH',
          ctes.map(([name, expression]) => `${context.escape(name)} AS (${expression})`).join(', '),
          originalGetQuery.call(context),
        ].join(' ');
      }

      return originalGetQuery.call(context);
    },
  });

  Object.defineProperty(qbPrototype, 'addCommonTableExpression', {
    value: function addCommonTableExpression<T>(expression: CommonTableExpression<T>, name: string) {
      const context = this as SelectQueryBuilder<unknown>; // For typization only
      if (!context.expressionMap.commonTableExpressions) context.expressionMap.commonTableExpressions = { };

      if (typeof expression === 'function') {
        const subQueryBuilder = expression(context.subQuery());
        context.setParameters(subQueryBuilder.getParameters());
        context.expressionMap.commonTableExpressions[name] = subQueryBuilder.getQuery();
      } else {
        context.expressionMap.commonTableExpressions[name] = expression;
      }

      return context;
    },
  });
}

type OptionableQueryBuilder = typeof SelectQueryBuilder
| typeof UpdateQueryBuilder
| typeof DeleteQueryBuilder
| typeof InsertQueryBuilder
| typeof RelationQueryBuilder;

type CommonTableExpression<T> = string | ((qb: SelectQueryBuilder<T>) => SelectQueryBuilder<T>);

export interface IQueryBuilderWithCommonTableExpressions<Entity> {
  addCommonTableExpression<T = Entity>(expression: CommonTableExpression<T>, name: string): this;
  getQuery(): string;
}

declare module 'typeorm' {
  export interface SelectQueryBuilder<Entity> extends IQueryBuilderWithCommonTableExpressions<Entity> { }
}

declare module 'typeorm/query-builder/QueryExpressionMap' {
  export interface QueryExpressionMap {
    commonTableExpressions: Record<string, string>;
  }
}

Usage

call patchTypeOrmQueryBuilderAddCommonTableExpressionsSupport some where at application startUp, to fully patch TypeORM's SelectQueryBuilder.

patchTypeOrmQueryBuilderAddCommonTableExpressionsSupport(); // This will patch SelectQueryBuilder as default

Use someWhere/at/some/repository.ts

qb
  .addCommonTableExpression(`SELECT * FROM some_table`, 'some_cte')
  .addSelect(/* ... */)

[mopcweb]

@timfrans u could try to use solution above

[67726e]

@mopcweb What version is this meant to work against? I've tried on 0.2.x and 0.3.x and in all cases it results in an error.

[ab958]

any update on that?

[Ginden]

https://typeorm.io/select-query-builder#common-table-expressions

[revskill10]

@Ginden How do we know if it supports a driver ?
I'm using SQL server.

[Ginden]

@revskill10 SQL Server is partially supported - https://github.com/typeorm/typeorm/blob/master/src/driver/sqlserver/SqlServerDriver.ts#L239

I wasn't able to implement writable CTEs, because of issues with non-standard OUTPUT clause.