Allow [uninitialized] Collections as values for filter queries

Author: pantajoeCreated Aug 15, 2022Updated Oct 15, 2022

Labelsenhancement

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 :heart:.

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:

ruby

# 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:

typescript

// 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:

typescript

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

typescript

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.

Source: mikro-orm/mikro-orm

View original on GitHub View discussion on GitHub