Computed column with alias is not mapping into TypeORM Entity

Question

I'm trying to get data from a table of a MySQL database using TypeORM in my Express.js project.

I'm using QueryBuilder to get data.

My implementation:

const result = await this.repository.createQueryBuilder("post")
                     .skip((request.PageNo - 1) * request.PageSize)
                     .take(request.PageSize)
                     .select([
                        "post.Id",
                        "post.Title",
                        "SUBSTRING(post.Content, 1, 150) AS post_Content",
                        "post.ReadTime",
                        "post.CreatedDate"
                     ])
                    .getRawAndEntities();

Result:

{
  raw: [
    TextRow {
      post_Id: '457457',
      post_CreatedDate: 2021-03-17T18:00:00.000Z,
      post_Title: 'This is a random title',
      post_ReadTime: 3,
      post_Content: "If you're looking for random paragraphs, you've come to the right place. When a random word or a random sentence isn't quite enough, the next logical "
    }
  ],
  entities: [
    Post {
      CreatedBy: '',
      CreatedDate: 2021-03-17T18:00:00.000Z,
      Content: '',
      Title: 'This is a random title',
      ReadTime: 3,
      IsFeatured: false,
      Id: '457457'
    }
  ]
}

Expectation: As you can see, I need a substring of the Content column. I've added alias as TypeORM convention, I think. But the column is not being mapped to the property.

I've also got the raw data, where you can see the substring of the column with alias is working.

Alternates I've tried:

"SUBSTRING(post.Content, 1, 150)"
"SUBSTRING(post.Content, 1, 150) AS Content"
"SUBSTRING(post.Content, 1, 150) AS post.Content"
"SUBSTRING(post.Content, 1, 150) AS `post.Content`"

But not a single one maps the Content column to the Content property of the Post entity.

Note: The Content column is only mapped when I'm not using any alias.

For trace: Generated Raw SQLs:

When not using alias

SELECT `post`.`Id` AS `post_Id`, `post`.`CreatedDate` AS `post_CreatedDate`, `post`.`Title` AS `post_Title`, `post`.`Content` AS `post_Content`, `post`.`ReadTime` AS `post_ReadTime` FROM `Posts` `post` LIMIT 10
When using alias

SELECT `post`.`Id` AS `post_Id`, `post`.`CreatedDate` AS `post_CreatedDate`, `post`.`Title` AS `post_Title`, `post`.`ReadTime` AS `post_ReadTime`, SUBSTRING(`post`.`Content`, 1, 150) AS `post_Content` FROM `Posts` `post` LIMIT 10

Please help!!!

Edit (Working Solution):

const result = await this.repository.createQueryBuilder("post")
                     .skip((request.PageNo - 1) * request.PageSize)
                     .take(request.PageSize)
                     .select([
                        "post.Id",
                        "post.Title",
                        "post.Content",
                        "SUBSTRING(post.Content, 1, 150) AS post_Content",
                        "post.ReadTime",
                        "post.CreatedDate"
                     ])
                    .getMany();

user_158675 · Answer

Many people over many years have asked the same question.

Examples:

Issue 296 Select additional computed columns (still open)
Issue 1822 Add custom select and map to property of entity (closed as duplicate)
Issue 7008 Unable to addSelect with computed result (still open)
PR 4703 Added support for selecting computed columns (still open)
PR 6855 Add select and map function to MySQL Driver (still open)

Comment dated 2018-03-26 by the typeorm author on Issue 1822 : "Official solution named addSelectAndMap will come into QueryBuilder in 0.3.0" (There is no sign of this yet).

On issue #1822 there is a comment with a workaround.

First, you have to add { select: false } to the computed column in your entity

@Column({ select: false } )
Content: string;

Then use addSelect(selection, alias) for the computed column (addSelect with alias overload)

addSelect("SUBSTRING(post.Content,1,3)", "post_Content")

Computed column with alias is not mapping into TypeORM Entity

1 Answers