TypeORM SQL SubQuery

user_11609269 0
Viewed 1577

I want to retrieve a list of maintenances, distincted by ids and sorted by date.

Basically, I'm trying to translate this SQL query (under PostgreSQL) :

SELECT * FROM 
(
  SELECT DISTINCT m.id, * 
  FROM maintenances AS m 
  LEFT JOIN maintenance_items AS mi ON mi.maintenance_id = m.id 
  LEFT JOIN maintenance_types AS mt ON mi.type = mt.id 
  WHERE bike_id = '152cd134-c868-4a9e-ad49-691569f5786d'
) AS foo 
ORDER BY foo.m_date;

as a TypeORM query. I tried this :

DbConnection.manager
    .createQueryBuilder<Maintenance>(Maintenance, 'maintenances')
    .select('*')
    .from(qb => qb.subQuery()
      .select('*')
      .from(Maintenance, 'maintenances')
      .where('maintenances.bikeID = :id', { id: id })
      .leftJoinAndSelect('maintenances.items', 'maintenance_items')
      .leftJoinAndSelect('maintenance_items.type', 'maintenance_types')
      .distinctOn(['maintenances.id']), 'foo')
    .orderBy('foo.m_date', 'DESC')

And I'm getting this error : Cannot get entity metadata for the given alias "foo". Cannot figure out why.

typeorm postgresql sql javascript
2 Answers

Your TypeOrm query is selecting from Maintenance table two times, once with createQueryBuilder<Maintenance>().Select('*'), and again in subQuery().

Much simpler is to use manager.query(sql) instead of manager.createQueryBuilder().

Example:

let bike_id = '152cd134-c868-4a9e-ad49-691569f5786d';

const sql = `SELECT * FROM 
(
  SELECT DISTINCT m.id, * 
  FROM maintenances AS m 
  LEFT JOIN maintenance_items AS mi ON mi.maintenance_id = m.id 
  LEFT JOIN maintenance_types AS mt ON mi.type = mt.id 
  WHERE bike_id = $1
) AS foo 
ORDER BY foo.m_date`;

let result = await DbConnection.manager.query(sql, [bike_id]);

console.log(result);

Points to note:

  • In TypeScript or JavaScript, use backticks `sql` to delimit the multiline-sql string
  • For PostgreSQL, if any column name in your raw SQL contains any uppercase characters, you need to enclose it in double-quotes
  • When using manager.query, the query parameters have to use syntax specific for the DBMS
DBMS Parameter syntax
PostgreSQL $1, $2
MySQL / MariaDB ?, ? ...
SQLite ?, ? ...
Microsoft SQL Server @0, @1 ...
user_1586750

You can use getrawOne() to retrieve data. This will not show error.

user_163343250
Related