Typeorm: joined relation data is not showing in final result

Question

I have a moving table which is in a one-to-many relation with the moving_activities table. moving_activities table records all the activities happening on the moving table. I'm trying to fetch all data from moving along with the latest activity from the moving_activites table.

My query:

this.repository
        .createQueryBuilder('moving')
        .leftJoinAndSelect(
          (subQuery) => {
            return subQuery
              .from(MovingActivityEntity, 'ma')
              .where('ma.moving_id = :movingId', { movingId: id })
              .orderBy('ma.created_at', 'DESC')
              .limit(1);
          },
          'movingActivities',
          'movingActivities.moving_id = moving.id'
        )
        .where('moving.id = :id', { id })
        .getOneOrFail();

MovingEnity

@Entity('movings')
export class MovingEntity {
  @PrimaryColumn()
  readonly id: string = ulid();

  @CreateDateColumn()
  readonly createdAt: Date;

  @UpdateDateColumn()
  readonly updatedAt: Date;

  @Column({ nullable: true })
  historyId?: string;

  @Column({ nullable: true })
  postId?: string;

  // @OneToMany((type) => MovingActivityEntity, (movingActivity) => movingActivity.moving)
  movingActivities: MovingActivityEntity;

}

MovingActivityEntity

@Entity('moving_activities')
export class MovingActivityEntity {
  @PrimaryColumn()
  readonly id: string = ulid();

  @CreateDateColumn()
  readonly createdAt: Date;

  @Column({ nullable: false })
  movingId: string;

  @ManyToOne((_type) => MovingEntity)
  @JoinColumn({ name: 'moving_id' })
  moving?: MovingEntity;

  @Column({
    type: 'enum',
    enum: activityTypes,
    default: 'undefined',
  })
  latestActivityType: ActivityType = 'undefined';

  constructor(movingId: string) {
    this.movingId = movingId;
  }
}

when I run the query I only get the movings table data. there is no moving_activities object.

MovingEntity {
  id: '01FVV2VPD7SZ87GWR4PQ840PRX',
  createdAt: 2022-02-14T03:01:47.049Z,
  updatedAt: 2022-04-22T07:20:05.000Z,
  historyId: null,
  postId: '01FVV2VPA8M12TV39W4861EVZV',
}

I have also tried to map the data in movingActivities with leftJoinAndMapOne with no luck.

If I use execute() in place of getOneOrFail() I get the intended result but execute() return the raw data.

Answer 1

Custom select work only with raw results. You can use also getRawOne().

The method leftJoinAndSelect say:

LEFT JOINs given subquery and add all selection properties to SELECT..

so not add the object but all properties to the selection. These added properties are not in the model that you are getting with getOne..., but you can return this data with getRawOne().

Maybe leftJoinAndMapOne can be what you looking for.

Try this query:

this.repository
        .createQueryBuilder('moving')
        .leftJoinAndMapOne('moving.movingActivities',
          (subQuery) => {
            return subQuery.select()
              .from(MovingActivityEntity, 'ma')
              .where('ma.moving_id = :movingId', { movingId: id })
              .orderBy('ma.created_at', 'DESC')
              .limit(1);
          },
          'movingActivities',
          'movingActivities.moving_id = moving.id'
        )
        .where('moving.id = :id', { id })
        .getOneOrFail();

There is also an old question for the same problem: 61275599

Edit: Checked in typeorm source code, actually in the current version if we use subquery the mapping is skipped. So the only way to get full results from join with sub query is using getRaw methods.