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"
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();
Many people over many years have asked the same question.
Examples:
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")