How to excute Raw SQL Query on NestJS framework using typeorm

Question

I am going to execute such a long query on the NestJS framework using Typeform. Please let me know how to execute this query.

select user.id, user.fullName, (select count(*) sendCnt from chat where senderId = user.id), (select count(*) recvCnt from chat where receiverId = user.id) from users user where user.role = 'Admin'

Answer 1

You can use query builder to create the required query -

return await getRepository(users)
.createQueryBuilder("user")
.where("user.role = 'Admin'")
.select("user.id as userId")
.addSelect("user.fullName as fullName")
.addSelect("(select count(*) sendCnt from chat where senderId = user.id) as sendCnt")
.addSelect("(select count(*) recvCnt from chat where receiverId = user.id) as recvCnt")
.printSql()
.getRawMany();