TypeORM: column must appear in the GROUP BY clause or be used in an aggregate function-CodePudding

I'm trying to work out why the following raw SQL works perfectly with PostGres, whereas the SQL generated via TypeORM does not.

Running this works:

SELECT symbol, MAX(created_at) AS created_at
FROM update_history
WHERE exchange = 'NYSE'
AND data_type = 'companyRecord'
GROUP BY symbol
ORDER BY created_at ASC

Example: https://dbfiddle.uk/yocl-rBq

Whereas, the TypeORM sql generated by the following:

const result = this.repository
  .createQueryBuilder('h1')
  .select(['MAX(h1.createdAt) AS created_at', 'h1.symbol'])
  .where('h1.exchange = :exchange', { exchange })
  .andWhere('h1.dataType = :dataType', { dataType })
  .groupBy('h1.symbol')
  .orderBy({ created_at: 'ASC' })
  .take(limit)
  .getMany();

/* Produces this:

SELECT "h1"."symbol" AS "h1_symbol", 
  MAX("h1"."created_at") AS created_at 
FROM "update_history" "h1" 
WHERE "h1"."exchange" = 'NYSE'
AND "h1"."data_type" = 'companyRecord' 
GROUP BY "h1"."symbol" 
ORDER BY created_at ASC LIMIT 5000
*/

Example (second select box): https://dbfiddle.uk/yocl-rBq

Returns the following error:

QueryFailedError: column "h1.id" must appear in the GROUP BY clause or be used in an aggregate function

If I add h1.id to the GROUP BY clause, the query no longer returns the correct result set, as it is effectively different.

As you can see from the DBFiddle links, both generated SQL queries work outside of TypeORM.

What am I missing here?

CodePudding user response：

You're using getMany() which always adds id field in the select. The query that you've pasted is most probably has been logged before adding getMany(). Try setting logging: true in your ormconfig.js and you'll see the exact query being fired.

You should use getRawMany() and you'll be able to get the exact query and also the desired result.