Is it possible to use subquery in leftJoinAndSelect in TypeORM

Question

So i've got a project on nestjs and using TypeORM. And my situation is i have a User which have One-to-May relation with UserSubscrption. Default find query added all subscriptions to User as it should be. So now i want to find one specific subscription, for example last added. So i build this query:

    const query = this.createQueryBuilder("user")
    .leftJoinAndSelect("user.settings", "settings")
    .leftJoinAndSelect( 
      subQuery => {
          return subQuery
              .select()
              .from(UserSubscription, "subscription")
              .where("subscription.userId = :id AND subscription.isConfirmed = :isConfirmed", { id, isConfirmed: true })
              .orderBy('"createdDate"', 'DESC')
              .limit(1);
      }, 'subscription', '"subscription"."userId" = "user"."id"')
    .where('user.id = :id', { id });
    const result = await query.getOne(); // await query.execute()

First, i try just execute the query and it works fine but all the data not structured

[
  {
    user_id: 1,
    user_username: 'name',
    ...
    settings_id: 1,
    settings_ifOnline: true,
    ...
    subscriptions_id: 1,
    subscriptions_subscriptionId: 1,
    ...
  }
]

So it's not good.

Then i try query.getOne() but it wont work as i want, it's lost all subscription data

User {
  id: 1,
  username: 'name',
  ...
  settings: UserNotificationSettings {
    id: 1,
    ifOnline: true,
    ...
  }
}

Also, i tried to add virtual field subscription to user entity and try to use leftJoinAndMapOne:

    ...
    .leftJoinAndSelect("user.settings", "settings")
    .leftJoinAndMapOne("user.subscription", 
      subQuery => {
          return subQuery
              .select()
              .from(UserSubscription, "subscription")
              .where("subscription.userId = :id AND subscription.isConfirmed = :isConfirmed", { id, isConfirmed: true })
              .orderBy('"createdDate"', 'DESC')
              .limit(1);
      }, 'subscription', '"subscription"."userId" = "user"."id"')
    ...

But with no luck. In docs it's said that "Subqueries are supported in FROM, WHERE and JOIN expressions." but no example is provided. So, i have no idea how to handle this i belive pretty much simple query with TypeORM. Any suggestions? i'm kinda stuck with this. May be there is some better way than buildibg queries? Thanks.

User.entity

 export class User extends BaseEntity {
  @PrimaryGeneratedColumn()
  id: number;
  ...

  // subscription: UserSubscription;

  @OneToMany(type => UserSubscription, subscriptions => subscriptions.user, { cascade:true })
  subscriptions: UserSubscription[];

  @OneToOne(type => UserNotificationSettings, settings => settings.user, { cascade:true })
  settings: UserNotificationSettings;
}

UserSubscription.entity

export class UserSubscription extends BaseEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @OneToOne(type => Subscription)
  @JoinColumn()
  subscription: Subscription

  @Column()
  subscriptionId: number

  @ManyToOne(type => User, user => user.subscriptions)
  user: User

  @Column({type: 'integer'})
  userId: number

  @Column({ type: 'boolean', default: false })
  isConfirmed: boolean

  ...
}

Zawad Zawad · Accepted Answer · 2020-09-25T17:47:23

Using the SubQueryFactory option does not automatically create the on clause as the condition as it cannot know what the underlying query's alias is that you're trying to join with. It doesn't have the context afforded by the metadata of .leftJoinAndSelect('user.photos', 'photos') because of how it's been written.

Instead, you have to explicitly define the condition. In your case:

const result = await userRepo
                     .createQueryBuilder('user')
                     .leftJoinAndSelect(
                         qb => qb
                            .select()
                            .from(UserPhotos, 'p')
                            .orderBy({ 'p.updatedAt': 'ASC' })
                            .limit(5),
                         'photos',
                         'photos.userId = user.id' // the answer
                     )
                     .getRawMany() // .getMany() seems not working

Here is the actual answer: https://github.com/typeorm/typeorm/issues/6767

UPDATE

I saw that .getMany() is not working when using SubQueryFactory. Insted you can use .getRawMany().

Please correct me if I'm wrong...

Is it possible to use subquery in leftJoinAndSelect in TypeORM

1 Answers

UPDATE