如何在带有select(SOLVED)的Typeorm中使用SUBSTR mysql函数

Question

我正在创建一个查询，它可以带来3个表关系结果

this.invoicesRepo
.createQueryBuilder('invoices')
.innerJoinAndSelect('invoices.client', 'client')
.innerJoinAndSelect('invoices.items', 'items')
.select(['invoices.id', 'client.name'])
.groupBy('invoices.id')
.getMany();

返回这些结果

[
{
"id": 1,
"client": {
"name": "Goku"
}
}
]

但是我想要像这样的结果

// I WANT RESULTS
[
{
"id": 1,
"client": {
"name": "Gok"  // just 3 start letters 
}
}
]

我写这个查询是为了这个目的，但它只是返回id

this.invoicesRepo
.createQueryBuilder('invoices')
.innerJoinAndSelect('invoices.client', 'client')
.innerJoinAndSelect('invoices.items', 'items')
.select(['invoices.id', 'SUBSTR(client.name,1,3)']) // not working
.groupBy('invoices.id')
.getMany();

如何在typeorm和mysql 中获得较高的结果

因此，我可以将其总结为一个简单的问题，即如何在类型中编写此查询

select SUBSTR(name,1,3),items.invoiceId from client
inner join invoices on invoices.clientId = client.id
inner join items on items.invoiceId = invoices.id
group by items.invoiceId;

Answer 1

我有一个解决方案

对于使用substr函数，我们应该使用typeorm提供的getRaw函数getRawOne((和getRawMany((

所以查询将像这个

this.invoicesRepo
.createQueryBuilder('invoices')
.innerJoinAndSelect('invoices.client', 'client')
.innerJoin('invoices.items', 'items')
.select('invoices.id')
.addSelect('SUBSTR(client.name,1,3)', 'client_name') //here
.groupBy('items.invoiceId')
.orderBy('invoices.created_at', 'ASC')
.getRawMany();  //these is necessary if u using function like substr,concat etc

输出

[
{
"invoices_id": 1,
"client_name": "Gok"
},
{
"invoices_id": 2,
"client_name": "Kha"
},
{
"invoices_id": 3,
"client_name": "Gok"
}
]