CODEWITHSUNDEEP
postgresqlnestjstypeorm
onlyanegg
onlyanegg

Reputation: 517

TypeORM QueryBuilder from clause with alias

I'm having trouble creating a materialized view in Postgres using the query builder. I'm selecting from multiple tables, joining the results, and then selecting from the final result. I've included a simplified version that shows the trouble I'm having.

The second parameter of the from method sets the alias for the select clause. The resulting SQL (shown below) has from (...) "Logins". Using double quotes around Logins is causing an error. Without them, the query runs, but with them it fails with SQL Error [42P01]: ERROR: missing FROM-clause entry for table "logins"

Can anyone help me with this?

View Entity:

@ViewEntity({
  expression: connection => {
    const loginQb = connection
      .createQueryBuilder()
      .from(LoginEvent, "LE")
      .select("COUNT(LE.id)", "num_logins");
    return connection
      .createQueryBuilder()
      .select('Logins."num_logins"')
      .from(`(${loginQb.getQuery()})`, "Logins");
  }
})
class MyView {
  ...
}

Resulting SQL:

SELECT Logins."num_logins" FROM (SELECT COUNT("LE"."id") AS "num_logins" FROM "LoginEvent" "LE") "Logins"

Upvotes: 0

Views: 2644

Answers (1)

vijayakumarpsg587
vijayakumarpsg587

Reputation: 1177

Not sure if you have the answer already. It seems you are missing alias in "num_logins" Cna you try it and see if this fixes the issue. Please look at the "num_logins". I havent tried it yet but it looks like that might be the issue

@ViewEntity({
  expression: connection => {
    const loginQb = connection
      .createQueryBuilder()
      .from(LoginEvent, "LE")
      .select("COUNT(LE.id)", "LE"."num_logins");
    return connection
      .createQueryBuilder()
      .select('Logins."num_logins"')
      .from(`(${loginQb.getQuery()})`, "Logins");
  }
})

Upvotes: 1

Related Questions