Allow [uninitialized] Collections as values for filter queries

[pantajoe]

Is your feature request related to a problem? Please describe.
No.

Describe the solution you'd like
First of all, I love MikroORM and many thanks for maintaining this awesome ORM ❤️.

Many ORMs in other languages, such as ActiveRecord in Ruby on Rails allow queries within queries in order to avoid having to send unnecessary queries to the database, even though their direct result is not necessary, but their result will only be used in other queries.
For instance:

# Consider a user has an n:m relationship with workspace (such as in Slack)
# In order to load workspaces both by given ids and workspaces the user is assigned to
# you can use two separate queries:
Workspace.where(id: [*ids]) + user.workspaces
# which would translate to:
# select * from "workspace" where id in (...);
# select * from "workspace" inner join "user_workspaces" on "user_workspaces"."workspace_id" = "workspace"."id" where "user_workspaces"."user_id" = :user.id;

# Or provide one query as an argument to another to avoid two separate queries
Workspace.where(id: [*ids]).or.where(Workspace.where(id: user.workspaces))
# which would translate to:
# select * from "workspace" inner join "user_workspaces" on "user_workspaces"."workspace_id" = "workspace"."id" where "user_workspaces"."user_id" = :user.id or id in (...);

With MikroORM, you cannot use the second approach. What I could imagine is to allow collections as a parameter for a value of a FilterQuery:

// Consider the following User entity
@Entity()
class User extends BaseEntity {
  // ...
  @ManyToMany(() => Workspace, (workspace) => workspace.users, { owner: true })
  workspaces = new Collection<Workspace>(this)
  // ...
}

// and the following Workspace entity
@Entity()
class Workspace extends BaseEntity {
  @PrimaryKey({ columnType: 'uuid', defaultRaw: 'gen_random_uuid()' })
  id: string

  @ManyToMany(() => User, (user) => user.workspaces)
  users = new Collection<User>(this)
}

const workspaces = await em.getRepository(Workspace).find({
  $or: [
    { id: { $in: [...ids] } },
    { id: user.workspaces },
  ],
})

instead of having to use the first approach of:

const userWorkspaceIds = (await user.workspaces.init()).getIdentifiers<string>()
const workspaces = await em.getRepository(Workspace).find({
  id: { $in: [...ids, ...userWorkspaceIds] },
})

Describe alternatives you've considered
I would consider this a QOL feature, especially if you can chain a non-evaluated where clause with something like

const workspaces = await em.getRepository(Workspace).find({
  $or: [
    { id: { $in: [...ids] } },
    { id: user.workspaces.where({ deactivated: true }) },
  ],
})

that would make it easier to describe such queries instead of having to resort to the QueryBuilder which is of course a viable solution.

Additional context
I could imagine of a Collection to store a lazily evaluated filter query (which is set by using a where method on a Collection) and simply use the information of the property name, join tables, entity parent, etc. to enhance the filter query of an EntityRepository.

[B4nan]

At first I wasnt really sure about this, but it could be actually quite simple, we already support subqueries on the right hand side of filter properties, so its just about mapping collection similar way.

[pantajoe]

Great to hear! I'm not sure if I have the time this year, but can I help in implementing this? And if so, could you point me into the direction of best place to start there and the code location where the query is formed?