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'
If you're using TypeORM you can inject the connection using @InjectConnection() and use query to run a raw query, as shown in the TypeORM docs
const rawData = await connection.query(`SELECT * FROM USERS`);
Assuming you're using @nestjs/typeorm, this.connection can be obtained through the @InjectConnection() decorator in the constructor
@Injectable()
export class FooService {
constructor(@InjectConnection() private readonly connection: Connection) {}
async doSomeQuery() {
return this.connection.query('SELECT * FROM USERS;');
}
}
As of Nest v9 (and somewhere in @nestjs/typeorm@8, rather late in the version) TypeORM v0.3.0 is used and @InjectConnection() is deprecated. @InjectDataSource() should be used instead
@InjectConnection() decorator is now deprecated. Use @InjectDataSource() now.you can use typeorm - DataSource module for execute raw SQL queries
please refer below example
import { DataSource } from 'typeorm';
export class <class name> {
constructor(
@InjectDataSource() private dataSource: DataSource,
) {}
async function_name () {
this.dataSource.query(<QUERY>)
}
}
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();
You can use this:
import { getManager } from 'typeorm';
const entityManager = getManager();
return entityManager.query(`SELECT * FROM users`)
On your case:
return entityManager.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'`)
