如何使用TypeORM QueryBuilder实现指定PostgreSQL子查询逻辑？

Let's break down how to fix your QueryBuilder code to match the original PostgreSQL query logic.

First, let's recap what your original SQL does: it uses DISTINCT ON (symbol) to get the most recent record per symbol (sorted by created_at DESC), then takes those records and sorts them by created_at ASC to grab the earliest 500 entries.

Issues with your current code: #

• You're selecting fields from the main history table instead of the subquery h (which contains the distinct per-symbol records)
• The subquery's orderBy has the wrong order: PostgreSQL requires the first ORDER BY field to match the DISTINCT ON field (here, symbol)
• You're adding an extra where clause on the main table that's redundant and breaks the logic

Fixed QueryBuilder Code: #

const result = await this.repository
  .createQueryBuilder()
  .select(['h.symbol', 'h.createdAt'])
  .from(
    (subQuery) => subQuery
      .select(['symbol', 'createdAt'])
      .distinctOn(['symbol'])
      .from(HistoryRecord, 'h')
      .where('h.exchange = :exchange', { exchange: 'TEST' })
      .andWhere('h.dataType = :dataType', { dataType: 'ANY' })
      .orderBy('h.symbol', 'ASC') // Must match DISTINCT ON field as first sort
      .addOrderBy('h.createdAt', 'DESC'), // Get the latest record per symbol
    'h'
  )
  .orderBy('h.createdAt', 'ASC')
  .take(500)
  .getMany(); // Don't forget to execute the query!

Key Details: #

1. DISTINCT ON and ORDER BY alignment: PostgreSQL enforces that the first field in your ORDER BY matches the DISTINCT ON field. This ensures that for each symbol, we keep the first record from the sorted list (which is the most recent one, thanks to createdAt DESC).
2. Build the query from the subquery: Instead of trying to join the subquery to the main table, we directly use the subquery as the source for our outer query—this matches the structure of your original SQL.
3. Execute the query: Don't forget to call getMany() (for entity objects) or getRawMany() (for raw SQL results) to run the query and get your data.

You can verify the generated SQL matches your original by adding .getSql() at the end instead of .getMany() to inspect the output.

内容的提问来源于stack exchange，提问作者jahilldev