TypeOrm ViewEntity query is incorrect

Question

I am following the TypeOrm documents on how to create a ViewEntity so I can generate and query a custom view of my database. However at runtime, the SQL generated is not what I expected. The docs mention that the expression fed to @ViewEntity() can be a query. Here is my model:

@ViewEntity({
  expression: `
SELECT t1.*, t1.CountOfA + t1.CountOfB AS Total
FROM (
SELECT q.CountOfA, q.CountOfB
FROM questions q
) AS t1`
})
export class CountViewEntity {
  @ViewColumn()
  CountOfA: number;

  @ViewColumn()
  CountOfB: number;

  @ViewColumn()
  Total: number;
}

So I expected that the Repository<CountViewEntity> would give me one record with CountOfA, CountOfB and Total for each row in my questions table. So I execute this:

this.countViewRepository.find();

However the following SQL gets generated:

SELECT CountViewEntity.CountOfA AS CountViewEntity_CountOfA, CountViewEntity.CountOfB AS CountViewEntity_CountOfB, CountViewEntity.Total AS CountViewEntity_Total FROM count_view_entity CountViewEntity

And I get an error about table count_view_entity not existing.

What am I doing wrong?

Edit:

hmm... It's been a day since I put up a bounty and the answer/comment I received got me thinking: I'm a MySQL newbie as well as a TypeORM newbie so maybe I just overlooked an assumption the TypeORM docs make. I thought that the SQL generated would query actual DB tables, but I just reread the docs, in particular this part:

View entity is a class that maps to a database view

It meant nothing to me before but I've just come across something called a Database View. Maybe I should create one first and then query against that? Feeling optimistic once again...

Answer 1

View entity is a class that maps to a database view. You can create a view entity by defining a new class and mark it with @ViewEntity():

Updated as per post owner

Obviously, you must create the database views first before calling it from TypeORM, which cause the error count_view_entity not existing

@ViewEntity() accepts following options:

• name - view name. If not specified, then view name is generated from entity class name.
• database - database name in selected DB server.
• schema - schema name.
• expression - view definition. Required parameter.

expression can be string with properly escaped columns and tables, depend on database used.

Try as below

@ViewEntity({
         expression: `
           SELECT t1.*, t1.CountOfA + t1.CountOfB AS Total
           FROM (
                SELECT q.CountOfA, q.CountOfB
                FROM questions q
               ) AS t1`
      })

export class CountViewEntity {

  @ViewColumn()
  CountOfA: number;

  @ViewColumn()
  CountOfB: number;

  @ViewColumn()
  Total: number;
}

Do you really need Sub Query as there is no point in selecting the same result in outer select.

 @ViewEntity({
        expression: `
            SELECT "t1"."CountOfA" AS "CountOfA", "t1"."CountOfB" AS "CountOfB",
                   "t1"."CountOfA" + "t1"."CountOfB" AS "Total"
            FROM "questions" "t1"
               `
            })

Also, you can use QueryBuilder

@ViewEntity({ 
    expression: (connection: Connection) => connection.createQueryBuilder()
        .select("t1.CountOfA", "CountOfA")
        .addSelect("t1.CountOfB", "CountOfB")
        .addSelect("t1.CountOfA" + "t1.CountOfB", "Total")
        .from(questions, "t1")
})