Query Builder sub-queries Issue

[amitkTechno]

I am facing a new issue which is related to Query Builder sub-queries
Here I have 5 tables relationship

1. User entity

@OneToMany(() => Channel, (channel) => channel.createdBy)
createdChannels = new Collection<Channel>(this);

@OneToMany(() => Channel, (channel) => channel.subscribedBy)
subscribedChannels = new Collection<Channel>(this);

2. Channel entity

@ManyToOne()
createdBy: User;

@ManyToOne({ nullable: true })
subscribedBy?: User;

@OneToMany(() => ChannelParticipant, (details) => details.channel)
participants = new Collection<ChannelParticipant>(this);

@OneToMany(() => Message, (message) => message.channel)
messages = new Collection<Message>(this);

@OneToMany(() => Favourite, (fav) => fav.channel)
favourites = new Collection<Favourite>(this);

3. Channel Participant Entity

@ManyToOne()
channel: Channel;

4. Favourite Entity

@ManyToOne()
channel: Channel;

5. Message Entity

@ManyToOne()
@Index()
channel: Channel;

@ManyToOne()
@Index()
user: User;

const msgSubquery = this.em
        .createQueryBuilder(Message, 'ms')
        .orderBy({ id: 'desc' })
        .limit(10);

const db = this.em
        .createQueryBuilder(Channel, 'c')
        .select('*')
        .leftJoinAndSelect(['c.messages', msgSubquery], 'm')
        .leftJoinAndSelect('c.participants', 'p')
        .leftJoinAndSelect('c.createdBy', 'cr')
        .leftJoinAndSelect('c.subscribedBy', 's')
        .leftJoinAndSelect('c.favourites', 'f')
        .orWhere([
          { 'c.createdBy': Number(userId) },
          { 'c.subscribedBy': Number(userId) },
          { 'p.user': Number(userId) }
        ]);

console.log(db.getQuery());

Result

select * from "channel" as "c" 
left join "channel_participant" as "p" on "c"."id" = "p"."channel_id" 
left join "user" as "cr" on "c"."created_by_id" = "cr"."id" 
left join "user" as "s" on "c"."subscribed_by_id" = "s"."id" 
left join (select "ms".* from "message" as "ms" order by "ms"."id" desc limit 10) as "m"  // missing condition
left join "favourite" as "f" on "c"."id" = "f"."channel_id" 
where (("c"."created_by_id" = 1) or ("c"."subscribed_by_id" = 1) or ("p"."user_id" = 1))

Expected behavior

select * from "channel" as "c" 
left join "channel_participant" as "p" on "c"."id" = "p"."channel_id" 
left join "user" as "cr" on "c"."created_by_id" = "cr"."id" 
left join "user" as "s" on "c"."subscribed_by_id" = "s"."id" 
left join (select "ms".* from "message" as "ms" order by "ms"."id" desc limit 10) as "m" on "c"."id" = "m"."channel_id" 
left join "favourite" as "f" on "c"."id" = "f"."channel_id" 
where (("c"."created_by_id" = 1) or ("c"."subscribed_by_id" = 1) or ("p"."user_id" = 1))

Versions

• NodeJs - 18.16.0
• Mikro-orm - 6.1.0
• Database - Postgres

Thanks @B4nan

[PrimeDominus]

Not sure if this helps, but this may have been fixed in a newer version of Mikro-ORM. Looking at the Changelog since 6.1.0 there has been some updates to how the Query Builder functions.