How do I change the alias of a column in Typeorm?

user_14348464 0
Viewed 563

I want to change the name of a field with my QueryBuilder in the response body i.e a field called id, I want it to output as staff_id, I am having difficulties with that.

import { getRepository, Like } from 'typeorm';
import { RoomEntity } from '@entity/room.entity';
import { HttpException } from '@exceptions/HttpException';
import { isEmpty } from '@utils/util';
import { Room } from '@/interfaces/room.interface';

class RoomService {
  public room = RoomEntity;

  public async findQueryRoom(): Promise<Room[]> {
    const rooms = await getRepository(this.room)
      .createQueryBuilder('room')
      .select(['room.id as staff_id', 'room.name'])
      .getMany();
    return rooms;
  }

 

}

export default RoomService;

In the controller, I have:

import { NextFunction, Request, Response } from 'express';
import { Room } from '@interfaces/room.interface';
import RoomService from '@services/room.service';

class RoomsController {
  public roomService = new RoomService();


  public getRoomsByQuery = async (req: Request, res: Response, next: NextFunction): Promise<void> => {
    try {
      const findAllRoomsData: Room[] = await this.roomService.findQueryRoom();

      res.status(200).json({ data: findAllRoomsData, message: 'found all' });
    } catch (error) {
      next(error);
    }
  };

  


 


 
}

export default RoomsController;

The response I have in my postman does not include the staff_id field, I see

"data": [
        {
            "name": "Soba"
        }
        ]

How can I solve this problem?

query-builder typeorm typescript orm sql
1 Answers

I don't know if you have gotten the solution already, but for those that are facing the same problem using select column alias with TypeORM. I face the same problem some time ago and below was my solution I don't know if it's a convincing solution but it solve my problem.

public async findQueryRoom(): Promise<Room[]> {
    const rooms = await getRepository(this.room)
      .createQueryBuilder('room')
      .select(['room.id AS staff_id', 'room.name AS name'])
      .groupBy("room.id")
      .getRawMany();
    return rooms;
  }

Why .groupBy("room.id") when .groupBy("room.id") was absent it keep on selecting each row as many as the total row for example if there are 5 rows in total it will return each row 5 times that is why I introduced .groupBy("room.id") to rid of that. For 'room.name AS name' if you leave 'room.name' it will return room_name instead of name.

user_23322500
Related