TypeORM: How to use sub queries in queryBuilder-CodePudding

I have the following PostgresSQL:

SELECT symbol, created_at
FROM (
  SELECT DISTINCT ON (symbol) symbol, created_at
  FROM update_history
  WHERE exchange = 'TEST' AND data_type = 'ANY'
  ORDER BY symbol, created_at DESC
) h
ORDER BY created_at ASC
LIMIT 500;

I'm trying to write this out using TypeORM's queryBuilder, but can't seem to work out how it translates to the above.

The closest I've got right now (which isn't correct, and doesn't return the correct data set) is the following:

const result = this.repository
  .createQueryBuilder('history')
  .select(['history.symbol', 'history.createdAt'])
  .where('history.exchange = :exchange', { exchange: 'TEST' })
  .addFrom(
    (subQuery) =>
      subQuery
        .select(['h.symbol', 'h.createdAt'])
        .distinctOn(['h.symbol'])
        .from(HistoryRecord, 'h')
        .where('h.exchange = :exchange', { exchange: 'TEST' })
        .andWhere('h.dataType = :dataType', { dataType: 'ANY' })
        .orderBy({ 'h.symbol': 'DESC', 'h.createdAt': 'DESC' }),
      'h',
    )
  .orderBy({ 'history.createdAt': 'ASC' })
  .take(500);

Can anyone help me out?

CodePudding user response：

You can avoid using this.repository at all as it'll add unnecessary additional from on history table. Also, you need to take care of the aliasing here. The alias h specified as the second argument of the addFrom method should be different from the one specified inside subquery from. To fix all these issues, you can do something like this:

const connection = getConnection();
const result = connection.createQueryBuilder()
  .select(['history.symbol', 'history.createdAt'])
  .addFrom(
    (qb) => {
      return qb.select(['h.symbol as symbol', 'h.createdAt as createdAt'])
        .distinctOn(['h.symbol'])
        .from(HistoryRecord, 'h')
        .where('h.exchange = :exchange', { exchange: 'TEST' })
        .andWhere('h.dataType = :dataType', { dataType: 'ANY' })
        .orderBy('h.symbol', 'DESC')
        .addOrderBy('h.createdAt', 'DESC')
      },
    'history'
  )
  .orderBy('history.createdAt', 'ASC')
  .take(500);

And it will generate exactly the query you want. If you don't want to use the connection object, you can simply use manager instead:

const result = getManager().createQueryBuilder()
  ...
  ...