javascript - TypeScript PostgreSQL TypeORM convert raw result to entity - Stack Overflow

admin管理员组

文章数量:1022467

Unfortunately it seems as if TypeORM does not provide a API to convert raw results from either EntityManager.query or QueryBuilder.execute to entities. However, indicates that you can use TypeORM internal classes like PlainObjectToNewEntityTransformer or RawSqlResultsToEntityTransformer.

I have tried both with little luck.

RawSqlResultsToEntityTransformer

 async updateOne(queryConfig: UpdateOne<User>): Promise<User> {
    const query: UpdateQueryBuilder<User> = this.createUpdateQuery(queryConfig);
    const { raw, affected } = await query.execute();

    if (!affected) {
      throw new HttpException('Could not update user', HttpStatus.CONFLICT);
    }

    const queryRunner = this.dataSource.createQueryRunner();

    const relationIdLoader = new RelationIdLoader(
      this.dataSource.manager.connection,
      queryRunner,
      query.expressionMap.relationIdAttributes,
    );

    const relationCountLoader = new RelationCountLoader(
      this.dataSource.manager.connection,
      queryRunner,
      query.expressionMap.relationCountAttributes,
    );

    const rawRelationIdResults = await relationIdLoader.load(raw);

    const rawRelationCountResults = await relationCountLoader.load(raw);

    const transformer = new RawSqlResultsToEntityTransformer(
      query.expressionMap,
      this.dataSource.driver,
      rawRelationIdResults,
      rawRelationCountResults,
    );

    console.log('raw', raw);

    const entities = transformer.transform(raw, query.expressionMap.mainAlias);

    console.log('entities', entities);

    return entities[0] as User;
  }

The console output

  console.log
    raw [
      {
        created_at: 2023-03-15T20:12:41.905Z,
        updated_at: 2023-03-15T20:12:42.003Z,
        id: 1,
        email: '[email protected]',
        first_name: 'user_1',
        last_name: 'user_1',
        organization_id: 1,
        role_id: 1
      }
    ]

      at UserRepository.updateOne (user/user.repository.ts:228:13)

  console.log
    entities []

PlainObjectToNewEntityTransformer

 async updateOne(queryConfig: UpdateOne<User>): Promise<User> {
    const query: UpdateQueryBuilder<User> = this.createUpdateQuery(queryConfig);
    const { raw, affected } = await query.execute();

    if (!affected) {
      throw new HttpException('Could not update user', HttpStatus.CONFLICT);
    }
 
    const metadata = this.dataSource.getMetadata(User);
    const transformer = new PlainObjectToNewEntityTransformer();

    const updatedUser: User = metadata.create(
      this.dataSource.createQueryRunner(),
    );

    transformer.transform(updatedUser, raw[0], metadata);

    console.log('raw', raw);
    console.log('updatedUser', updatedUser);

    return updatedUser;
}

Console output

  console.log
    [
      {
        created_at: 2023-03-15T20:37:33.440Z,
        updated_at: 2023-03-15T20:37:33.533Z,
        id: 1,
        email: '[email protected]',
        first_name: 'user_1',
        last_name: 'user_1',
        organization_id: 1,
        role_id: 1
      }
    ]

      at UserRepository.updateOne (user/user.repository.ts:244:13)

  console.log
    x User { id: 1, email: '[email protected]' }

the user entity

@Entity({ name: 'user' })
export class User extends BaseEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'varchar', name: 'email', nullable: false })
  email: string;

  @Column({ type: 'varchar', name: 'first_name', nullable: false })
  firstName: string;

  @Column({ type: 'varchar', name: 'last_name', nullable: false })
  lastName: string;

  @Column({ type: 'varchar', name: 'password', nullable: false })
  password: string;

  @Column({ name: 'organization_id', type: 'int', nullable: true })
  organizationId: number;

  @ManyToOne(() => Organization, (organization) => organization.users, {
    onDelete: 'CASCADE',
  })
  @JoinColumn({ name: 'organization_id', referencedColumnName: 'id' })
  organization: Organization;

  @Column({ name: 'role_id', type: 'int', nullable: false })
  roleId: number;

  @ManyToOne(() => Role, (role) => role.users, {
    onDelete: 'CASCADE',
  })
  @JoinColumn({ name: 'role_id', referencedColumnName: 'id' })
  role: Role;
}

How can I transform raw results from EntityManager.query or a query builder result (update, delete, or insert with QueryBuilder.returning('*') with either PlainObjectToNewEntityTransformer or RawSqlResultsToEntityTransformer?

Unfortunately it seems as if TypeORM does not provide a API to convert raw results from either EntityManager.query or QueryBuilder.execute to entities. However, https://github./typeorm/typeorm/issues/6803 indicates that you can use TypeORM internal classes like PlainObjectToNewEntityTransformer or RawSqlResultsToEntityTransformer.

I have tried both with little luck.

RawSqlResultsToEntityTransformer

 async updateOne(queryConfig: UpdateOne<User>): Promise<User> {
    const query: UpdateQueryBuilder<User> = this.createUpdateQuery(queryConfig);
    const { raw, affected } = await query.execute();

    if (!affected) {
      throw new HttpException('Could not update user', HttpStatus.CONFLICT);
    }

    const queryRunner = this.dataSource.createQueryRunner();

    const relationIdLoader = new RelationIdLoader(
      this.dataSource.manager.connection,
      queryRunner,
      query.expressionMap.relationIdAttributes,
    );

    const relationCountLoader = new RelationCountLoader(
      this.dataSource.manager.connection,
      queryRunner,
      query.expressionMap.relationCountAttributes,
    );

    const rawRelationIdResults = await relationIdLoader.load(raw);

    const rawRelationCountResults = await relationCountLoader.load(raw);

    const transformer = new RawSqlResultsToEntityTransformer(
      query.expressionMap,
      this.dataSource.driver,
      rawRelationIdResults,
      rawRelationCountResults,
    );

    console.log('raw', raw);

    const entities = transformer.transform(raw, query.expressionMap.mainAlias);

    console.log('entities', entities);

    return entities[0] as User;
  }

The console output

  console.log
    raw [
      {
        created_at: 2023-03-15T20:12:41.905Z,
        updated_at: 2023-03-15T20:12:42.003Z,
        id: 1,
        email: '[email protected]',
        first_name: 'user_1',
        last_name: 'user_1',
        organization_id: 1,
        role_id: 1
      }
    ]

      at UserRepository.updateOne (user/user.repository.ts:228:13)

  console.log
    entities []

PlainObjectToNewEntityTransformer

 async updateOne(queryConfig: UpdateOne<User>): Promise<User> {
    const query: UpdateQueryBuilder<User> = this.createUpdateQuery(queryConfig);
    const { raw, affected } = await query.execute();

    if (!affected) {
      throw new HttpException('Could not update user', HttpStatus.CONFLICT);
    }
 
    const metadata = this.dataSource.getMetadata(User);
    const transformer = new PlainObjectToNewEntityTransformer();

    const updatedUser: User = metadata.create(
      this.dataSource.createQueryRunner(),
    );

    transformer.transform(updatedUser, raw[0], metadata);

    console.log('raw', raw);
    console.log('updatedUser', updatedUser);

    return updatedUser;
}

Console output

  console.log
    [
      {
        created_at: 2023-03-15T20:37:33.440Z,
        updated_at: 2023-03-15T20:37:33.533Z,
        id: 1,
        email: '[email protected]',
        first_name: 'user_1',
        last_name: 'user_1',
        organization_id: 1,
        role_id: 1
      }
    ]

      at UserRepository.updateOne (user/user.repository.ts:244:13)

  console.log
    x User { id: 1, email: '[email protected]' }

the user entity

@Entity({ name: 'user' })
export class User extends BaseEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'varchar', name: 'email', nullable: false })
  email: string;

  @Column({ type: 'varchar', name: 'first_name', nullable: false })
  firstName: string;

  @Column({ type: 'varchar', name: 'last_name', nullable: false })
  lastName: string;

  @Column({ type: 'varchar', name: 'password', nullable: false })
  password: string;

  @Column({ name: 'organization_id', type: 'int', nullable: true })
  organizationId: number;

  @ManyToOne(() => Organization, (organization) => organization.users, {
    onDelete: 'CASCADE',
  })
  @JoinColumn({ name: 'organization_id', referencedColumnName: 'id' })
  organization: Organization;

  @Column({ name: 'role_id', type: 'int', nullable: false })
  roleId: number;

  @ManyToOne(() => Role, (role) => role.users, {
    onDelete: 'CASCADE',
  })
  @JoinColumn({ name: 'role_id', referencedColumnName: 'id' })
  role: Role;
}

How can I transform raw results from EntityManager.query or a query builder result (update, delete, or insert with QueryBuilder.returning('*') with either PlainObjectToNewEntityTransformer or RawSqlResultsToEntityTransformer?

• javascript
• typescript
• postgresql
• typeorm

Share Improve this question Follow edited Mar 21, 2023 at 21:09 Jonas Grønbek asked Mar 15, 2023 at 21:03 Jonas GrønbekJonas Grønbek 2,04944 gold badges2929 silver badges5858 bronze badges

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 1

RawSqlResultsToEntityTransformer is the correct way to do this.

But it's only known to work with SelectQueryBuilder. I suspect the reason you're having trouble in the given example is that you are trying to map the results from an UpdateQueryBuilder, which might not produce the metadata required to perform the mapping.

PlainObjectToNewEntityTransformer is not the right tool. It has a different purpose, which is to turn a plain object representing an entity into a class instance of that entity. This won't do any property<->column name mapping at all, as you observed.

The easiest solution in this scenario is going to be to run the update first, get the IDs of the affected rows, and then use a SelectQueryBuilder to fetch the data from those rows that you want to map to entities.

I was wondering the same thing and came up with this solution. Maybe this will help you too.

This solution is a) only to clarify the principle and can be further optimized and b) has not yet been put through its paces.

const result = await qb.returning('*').execute();
const columnsMap = new Map<string, string>(
  this.repo.metadata.columns.map((c) => [c.databaseName, c.propertyName]),
);

const entities = result.raw.map((raw) => {
  const obj = this.repo.create();

  for (const [key, value] of Object.entries(raw)) {
    const propertyName = columnsMap.get(key);
    if (!propertyName) continue;

    (obj as any)[propertyName] = value;
  }

  return obj;
});

This is a quick solution that could be helpful. But it still required to additional develop.

export class RawSqlTypeormHelper {
  public rawToEntity<TEntity extends typeof BaseEntity>(
    metadata: Pick<EntityMetadata, 'columns'>,
    record: Record<string, unknown>,
    entity: TEntity,
  ): TEntity {
    const columnsMapper = new Map(
      metadata.columns.map((v) => {
        let toTypeCb: (data: unknown) => unknown = (data) => data;
        if (v.type instanceof Function) {
          toTypeCb = (data) => (v.type as any)(data);
        } else {
          switch (true) {
            case ['varchar', 'enum'].includes(v.type as string): {
              toTypeCb = String;
              break;
            }
            case ['int8'].includes(v.type as string): {
              toTypeCb = Number;
              break;
            }
            case ['boolean'].includes(v.type as string): {
              toTypeCb = (data) => data === 'true';
              break;
            }
            case ['timestamp with time zone'].includes(v.type as string): {
              toTypeCb = (data) => new Date(data as string);
              break;
            }
          }
        }

        const columnName = v.propertyPath.includes('.')
          ? v.propertyPath.slice(0, v.propertyPath.indexOf('.'))
          : v.propertyPath;
        /**
         *    'role.id' -> 'role'
         * */
        return [
          v.databaseNameWithoutPrefixes,
          /**
           *  Database name in the database without embedded prefixes applied.
           * */
          {
            name: columnName,
            /**
             *  Gets full path to this column property (including column property name).
             *  Full path is relevant when column is used in embeds (one or multiple nested).
             *  For example it will return "counters.subcounters.likes".
             *  If property is not in embeds then it returns just property name of the column.
             *
             *  Same as property path, but dots are replaced with '_'. Used in query builder statements.
             * */
            toTypeCb: toTypeCb,
            isArray: v.isArray,
          },
        ];
      }),
    );

    const convertedRecord = Object.fromEntries(
      Object.entries(record).map(([key, value]) => {
        const mapper = columnsMapper.get(key);
        if (!mapper) {
          return [key, value];
        }

        return [mapper.name, mapper.isArray ? value.map((v) => mapper.toTypeCb(v)) : mapper.toTypeCb(value)];
      }),
    );

    return entity.getRepository().merge(new entity(), convertedRecord as unknown as TEntity) as unknown as TEntity;
  }
}

and then use it like that:

const rawSqlTypeormHelper = new RawSqlTypeormHelper();

export const DepartmentRepository = (dataSource: DataSource) =>
  dataSource.getRepository(DepartmentEntity).extend({
    async findByIdWithWorkers(
      this: Repository<DepartmentEntity>,
      id: number,
    ): Promise<IDepartmentWithWorkers | undefined> {
      const result: IDepartmentWithWorkers | undefined = await this.createQueryBuilder(DEPARTMENTS_TABLE_NAME)
        .select([
          `${DEPARTMENTS_TABLE_NAME}.*`,
          `array_agg(
            json_build_object(
              'user', (row_to_json(${USERS_TABLE_NAME}.*)::jsonb - 'pin_code'),
              'role', row_to_json(${ROLES_TABLE_NAME}.*)
            )
          ) AS "workers"`,
        ])
        .where(`${DEPARTMENTS_TABLE_NAME}.id = :id`, { id })
        .innerJoin(`${DEPARTMENTS_TABLE_NAME}.workers`, 'roles')
        .innerJoin(USERS_TABLE_NAME, 'users', 'roles.user_id = users.id')
        .groupBy(`${DEPARTMENTS_TABLE_NAME}.id`)
        .withDeleted()
        .getRawOne();

      if (result) {
        return {
          ...rawSqlTypeormHelper.rawToEntity(DepartmentEntity.getRepository().metadata, result as {}, DepartmentEntity),
          workers: result.workers.map((worker) => {
            return {
              user: rawSqlTypeormHelper.rawToEntity(UserEntity.getRepository().metadata, worker.user, UserEntity),
              role: rawSqlTypeormHelper.rawToEntity(RoleEntity.getRepository().metadata, worker.role as {}, RoleEntity),
            };
          }),
        } as unknown as IDepartmentWithWorkers;
      }

      return result;
    },
  });

Unfortunately it seems as if TypeORM does not provide a API to convert raw results from either EntityManager.query or QueryBuilder.execute to entities. However, indicates that you can use TypeORM internal classes like PlainObjectToNewEntityTransformer or RawSqlResultsToEntityTransformer.

I have tried both with little luck.

RawSqlResultsToEntityTransformer

 async updateOne(queryConfig: UpdateOne<User>): Promise<User> {
    const query: UpdateQueryBuilder<User> = this.createUpdateQuery(queryConfig);
    const { raw, affected } = await query.execute();

    if (!affected) {
      throw new HttpException('Could not update user', HttpStatus.CONFLICT);
    }

    const queryRunner = this.dataSource.createQueryRunner();

    const relationIdLoader = new RelationIdLoader(
      this.dataSource.manager.connection,
      queryRunner,
      query.expressionMap.relationIdAttributes,
    );

    const relationCountLoader = new RelationCountLoader(
      this.dataSource.manager.connection,
      queryRunner,
      query.expressionMap.relationCountAttributes,
    );

    const rawRelationIdResults = await relationIdLoader.load(raw);

    const rawRelationCountResults = await relationCountLoader.load(raw);

    const transformer = new RawSqlResultsToEntityTransformer(
      query.expressionMap,
      this.dataSource.driver,
      rawRelationIdResults,
      rawRelationCountResults,
    );

    console.log('raw', raw);

    const entities = transformer.transform(raw, query.expressionMap.mainAlias);

    console.log('entities', entities);

    return entities[0] as User;
  }

The console output

  console.log
    raw [
      {
        created_at: 2023-03-15T20:12:41.905Z,
        updated_at: 2023-03-15T20:12:42.003Z,
        id: 1,
        email: '[email protected]',
        first_name: 'user_1',
        last_name: 'user_1',
        organization_id: 1,
        role_id: 1
      }
    ]

      at UserRepository.updateOne (user/user.repository.ts:228:13)

  console.log
    entities []

PlainObjectToNewEntityTransformer

 async updateOne(queryConfig: UpdateOne<User>): Promise<User> {
    const query: UpdateQueryBuilder<User> = this.createUpdateQuery(queryConfig);
    const { raw, affected } = await query.execute();

    if (!affected) {
      throw new HttpException('Could not update user', HttpStatus.CONFLICT);
    }
 
    const metadata = this.dataSource.getMetadata(User);
    const transformer = new PlainObjectToNewEntityTransformer();

    const updatedUser: User = metadata.create(
      this.dataSource.createQueryRunner(),
    );

    transformer.transform(updatedUser, raw[0], metadata);

    console.log('raw', raw);
    console.log('updatedUser', updatedUser);

    return updatedUser;
}

Console output

  console.log
    [
      {
        created_at: 2023-03-15T20:37:33.440Z,
        updated_at: 2023-03-15T20:37:33.533Z,
        id: 1,
        email: '[email protected]',
        first_name: 'user_1',
        last_name: 'user_1',
        organization_id: 1,
        role_id: 1
      }
    ]

      at UserRepository.updateOne (user/user.repository.ts:244:13)

  console.log
    x User { id: 1, email: '[email protected]' }

the user entity

@Entity({ name: 'user' })
export class User extends BaseEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'varchar', name: 'email', nullable: false })
  email: string;

  @Column({ type: 'varchar', name: 'first_name', nullable: false })
  firstName: string;

  @Column({ type: 'varchar', name: 'last_name', nullable: false })
  lastName: string;

  @Column({ type: 'varchar', name: 'password', nullable: false })
  password: string;

  @Column({ name: 'organization_id', type: 'int', nullable: true })
  organizationId: number;

  @ManyToOne(() => Organization, (organization) => organization.users, {
    onDelete: 'CASCADE',
  })
  @JoinColumn({ name: 'organization_id', referencedColumnName: 'id' })
  organization: Organization;

  @Column({ name: 'role_id', type: 'int', nullable: false })
  roleId: number;

  @ManyToOne(() => Role, (role) => role.users, {
    onDelete: 'CASCADE',
  })
  @JoinColumn({ name: 'role_id', referencedColumnName: 'id' })
  role: Role;
}

How can I transform raw results from EntityManager.query or a query builder result (update, delete, or insert with QueryBuilder.returning('*') with either PlainObjectToNewEntityTransformer or RawSqlResultsToEntityTransformer?

Unfortunately it seems as if TypeORM does not provide a API to convert raw results from either EntityManager.query or QueryBuilder.execute to entities. However, https://github./typeorm/typeorm/issues/6803 indicates that you can use TypeORM internal classes like PlainObjectToNewEntityTransformer or RawSqlResultsToEntityTransformer.

I have tried both with little luck.

RawSqlResultsToEntityTransformer

 async updateOne(queryConfig: UpdateOne<User>): Promise<User> {
    const query: UpdateQueryBuilder<User> = this.createUpdateQuery(queryConfig);
    const { raw, affected } = await query.execute();

    if (!affected) {
      throw new HttpException('Could not update user', HttpStatus.CONFLICT);
    }

    const queryRunner = this.dataSource.createQueryRunner();

    const relationIdLoader = new RelationIdLoader(
      this.dataSource.manager.connection,
      queryRunner,
      query.expressionMap.relationIdAttributes,
    );

    const relationCountLoader = new RelationCountLoader(
      this.dataSource.manager.connection,
      queryRunner,
      query.expressionMap.relationCountAttributes,
    );

    const rawRelationIdResults = await relationIdLoader.load(raw);

    const rawRelationCountResults = await relationCountLoader.load(raw);

    const transformer = new RawSqlResultsToEntityTransformer(
      query.expressionMap,
      this.dataSource.driver,
      rawRelationIdResults,
      rawRelationCountResults,
    );

    console.log('raw', raw);

    const entities = transformer.transform(raw, query.expressionMap.mainAlias);

    console.log('entities', entities);

    return entities[0] as User;
  }

The console output

  console.log
    raw [
      {
        created_at: 2023-03-15T20:12:41.905Z,
        updated_at: 2023-03-15T20:12:42.003Z,
        id: 1,
        email: '[email protected]',
        first_name: 'user_1',
        last_name: 'user_1',
        organization_id: 1,
        role_id: 1
      }
    ]

      at UserRepository.updateOne (user/user.repository.ts:228:13)

  console.log
    entities []

PlainObjectToNewEntityTransformer

 async updateOne(queryConfig: UpdateOne<User>): Promise<User> {
    const query: UpdateQueryBuilder<User> = this.createUpdateQuery(queryConfig);
    const { raw, affected } = await query.execute();

    if (!affected) {
      throw new HttpException('Could not update user', HttpStatus.CONFLICT);
    }
 
    const metadata = this.dataSource.getMetadata(User);
    const transformer = new PlainObjectToNewEntityTransformer();

    const updatedUser: User = metadata.create(
      this.dataSource.createQueryRunner(),
    );

    transformer.transform(updatedUser, raw[0], metadata);

    console.log('raw', raw);
    console.log('updatedUser', updatedUser);

    return updatedUser;
}

Console output

  console.log
    [
      {
        created_at: 2023-03-15T20:37:33.440Z,
        updated_at: 2023-03-15T20:37:33.533Z,
        id: 1,
        email: '[email protected]',
        first_name: 'user_1',
        last_name: 'user_1',
        organization_id: 1,
        role_id: 1
      }
    ]

      at UserRepository.updateOne (user/user.repository.ts:244:13)

  console.log
    x User { id: 1, email: '[email protected]' }

the user entity

@Entity({ name: 'user' })
export class User extends BaseEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ type: 'varchar', name: 'email', nullable: false })
  email: string;

  @Column({ type: 'varchar', name: 'first_name', nullable: false })
  firstName: string;

  @Column({ type: 'varchar', name: 'last_name', nullable: false })
  lastName: string;

  @Column({ type: 'varchar', name: 'password', nullable: false })
  password: string;

  @Column({ name: 'organization_id', type: 'int', nullable: true })
  organizationId: number;

  @ManyToOne(() => Organization, (organization) => organization.users, {
    onDelete: 'CASCADE',
  })
  @JoinColumn({ name: 'organization_id', referencedColumnName: 'id' })
  organization: Organization;

  @Column({ name: 'role_id', type: 'int', nullable: false })
  roleId: number;

  @ManyToOne(() => Role, (role) => role.users, {
    onDelete: 'CASCADE',
  })
  @JoinColumn({ name: 'role_id', referencedColumnName: 'id' })
  role: Role;
}

How can I transform raw results from EntityManager.query or a query builder result (update, delete, or insert with QueryBuilder.returning('*') with either PlainObjectToNewEntityTransformer or RawSqlResultsToEntityTransformer?

• javascript
• typescript
• postgresql
• typeorm

Share Improve this question Follow edited Mar 21, 2023 at 21:09 Jonas Grønbek asked Mar 15, 2023 at 21:03 Jonas GrønbekJonas Grønbek 2,04944 gold badges2929 silver badges5858 bronze badges

Add a ment  | 

3 Answers 3

Sorted by: Reset to default 1

RawSqlResultsToEntityTransformer is the correct way to do this.

But it's only known to work with SelectQueryBuilder. I suspect the reason you're having trouble in the given example is that you are trying to map the results from an UpdateQueryBuilder, which might not produce the metadata required to perform the mapping.

PlainObjectToNewEntityTransformer is not the right tool. It has a different purpose, which is to turn a plain object representing an entity into a class instance of that entity. This won't do any property<->column name mapping at all, as you observed.

The easiest solution in this scenario is going to be to run the update first, get the IDs of the affected rows, and then use a SelectQueryBuilder to fetch the data from those rows that you want to map to entities.

I was wondering the same thing and came up with this solution. Maybe this will help you too.

This solution is a) only to clarify the principle and can be further optimized and b) has not yet been put through its paces.

const result = await qb.returning('*').execute();
const columnsMap = new Map<string, string>(
  this.repo.metadata.columns.map((c) => [c.databaseName, c.propertyName]),
);

const entities = result.raw.map((raw) => {
  const obj = this.repo.create();

  for (const [key, value] of Object.entries(raw)) {
    const propertyName = columnsMap.get(key);
    if (!propertyName) continue;

    (obj as any)[propertyName] = value;
  }

  return obj;
});

This is a quick solution that could be helpful. But it still required to additional develop.

export class RawSqlTypeormHelper {
  public rawToEntity<TEntity extends typeof BaseEntity>(
    metadata: Pick<EntityMetadata, 'columns'>,
    record: Record<string, unknown>,
    entity: TEntity,
  ): TEntity {
    const columnsMapper = new Map(
      metadata.columns.map((v) => {
        let toTypeCb: (data: unknown) => unknown = (data) => data;
        if (v.type instanceof Function) {
          toTypeCb = (data) => (v.type as any)(data);
        } else {
          switch (true) {
            case ['varchar', 'enum'].includes(v.type as string): {
              toTypeCb = String;
              break;
            }
            case ['int8'].includes(v.type as string): {
              toTypeCb = Number;
              break;
            }
            case ['boolean'].includes(v.type as string): {
              toTypeCb = (data) => data === 'true';
              break;
            }
            case ['timestamp with time zone'].includes(v.type as string): {
              toTypeCb = (data) => new Date(data as string);
              break;
            }
          }
        }

        const columnName = v.propertyPath.includes('.')
          ? v.propertyPath.slice(0, v.propertyPath.indexOf('.'))
          : v.propertyPath;
        /**
         *    'role.id' -> 'role'
         * */
        return [
          v.databaseNameWithoutPrefixes,
          /**
           *  Database name in the database without embedded prefixes applied.
           * */
          {
            name: columnName,
            /**
             *  Gets full path to this column property (including column property name).
             *  Full path is relevant when column is used in embeds (one or multiple nested).
             *  For example it will return "counters.subcounters.likes".
             *  If property is not in embeds then it returns just property name of the column.
             *
             *  Same as property path, but dots are replaced with '_'. Used in query builder statements.
             * */
            toTypeCb: toTypeCb,
            isArray: v.isArray,
          },
        ];
      }),
    );

    const convertedRecord = Object.fromEntries(
      Object.entries(record).map(([key, value]) => {
        const mapper = columnsMapper.get(key);
        if (!mapper) {
          return [key, value];
        }

        return [mapper.name, mapper.isArray ? value.map((v) => mapper.toTypeCb(v)) : mapper.toTypeCb(value)];
      }),
    );

    return entity.getRepository().merge(new entity(), convertedRecord as unknown as TEntity) as unknown as TEntity;
  }
}

and then use it like that:

const rawSqlTypeormHelper = new RawSqlTypeormHelper();

export const DepartmentRepository = (dataSource: DataSource) =>
  dataSource.getRepository(DepartmentEntity).extend({
    async findByIdWithWorkers(
      this: Repository<DepartmentEntity>,
      id: number,
    ): Promise<IDepartmentWithWorkers | undefined> {
      const result: IDepartmentWithWorkers | undefined = await this.createQueryBuilder(DEPARTMENTS_TABLE_NAME)
        .select([
          `${DEPARTMENTS_TABLE_NAME}.*`,
          `array_agg(
            json_build_object(
              'user', (row_to_json(${USERS_TABLE_NAME}.*)::jsonb - 'pin_code'),
              'role', row_to_json(${ROLES_TABLE_NAME}.*)
            )
          ) AS "workers"`,
        ])
        .where(`${DEPARTMENTS_TABLE_NAME}.id = :id`, { id })
        .innerJoin(`${DEPARTMENTS_TABLE_NAME}.workers`, 'roles')
        .innerJoin(USERS_TABLE_NAME, 'users', 'roles.user_id = users.id')
        .groupBy(`${DEPARTMENTS_TABLE_NAME}.id`)
        .withDeleted()
        .getRawOne();

      if (result) {
        return {
          ...rawSqlTypeormHelper.rawToEntity(DepartmentEntity.getRepository().metadata, result as {}, DepartmentEntity),
          workers: result.workers.map((worker) => {
            return {
              user: rawSqlTypeormHelper.rawToEntity(UserEntity.getRepository().metadata, worker.user, UserEntity),
              role: rawSqlTypeormHelper.rawToEntity(RoleEntity.getRepository().metadata, worker.role as {}, RoleEntity),
            };
          }),
        } as unknown as IDepartmentWithWorkers;
      }

      return result;
    },
  });

本文标签： javascriptTypeScript PostgreSQL TypeORM convert raw result to entityStack Overflow