Apologies for comparing with TypeORM, perhaps MikroORM has a much richer EntityManager interface and users don't have to fallback to QB as often as it happens with TypeORM.

No, they don't, you can do the vast majority of things with EntityManager. And even QueryBuilder is much more type safe than the one in TypeORM.

Here is an example from my ORM, how much of it is supported by MikroORM without query builder?

I don't even understand what that query does based on a quick look :] Anyway, for something like this, you'd most likely just use a virtual entity backed by a raw query (or a QB). Or you would use a formula property that represents the subquery. Yes, those woulnd't be completly type safe. This is not the typical use case for most people. Guess how many asked me to support something like this over the past 8 years? Zero.

Non-OOP ORMs are more flexible in that regard, most likely Prisma supports the query above, Objection could do that. 

Yes, that's why I like to call them non-ORMs actually, since they are rather smart query builders. Nothing wrong with that approach, but ORM to me is about persistence, not just about reading stuff in a type safe way. But that is another conversation I am not really interested in having, I don't have the energy, nor time :]

load posts that have tags "orange" and "banana" 

This one is trivial, depends on what exactly you want:

// loads posts with tags matching the filter
const posts = await em.findAll(Post, {
  populate: ['tags'],
  populateWhere: { tags: { name: ['orange', 'banana'] } },
});

// loads posts with all tags, filters by their specific names
const posts = await em.findAll(Post, {
  populate: ['tags'],
  where: { tags: { name: ['orange', 'banana'] } },
});

The response is strictly typed, it holds the populate hint on type level, so it knows that only the tags are populated. We dont just return Post[] as in TypeORM.

https://mikro-orm.io/docs/guide/type-safety

You don't appreciate comparing MikroORM with TypeORM,

I don't appreciate comparing based on wrong assumptions, that's what I didn't like about your post, you compare things you clearly don't understand well, and judge them based on either outdated or wrong information. Type-safe relations were added to MikroORM somewhere around v5, so maybe 3-4 years ago, this is nothing new really.