Find with multiple $or statements builds incorrect query

[NicolasDesmedt]

Describe the bug
EDIT: @Filter is not causing this, see bottom of issue.
Using $or statements in pre-defined filter criteria attached to entities (@Filter), while using $or statements in the conditions object of the find method, causes certain filters/conditions to be left out of the find method.

I found that wrapping the $or statement in the conditions object in an $and statement fixes the issue. But this shouldn't be necessary.

Given simplified entities

@Entity()
@Filter({
  name: "agefilter",
  cond: { $or: [{ age: 18 }, { age: 21 }] },
})
class User {
  @Property()
  public firstName: string;

  @Property()
  public lastName: string;

  @Property()
  public age: number;
}

@Entity()
@Filter({
  name: "userfilter",
  cond: { user: { $or: [{ firstName: "name" }, { lastName: "name" }] } },
})
class Membership {
  @ManyToOne(() => User)
  public user: User;

  @Property()
  public role: string;
}

Following find methods result in incorrect queries:

await em.find(
        User,
        { $or: [{ firstName: "name" }, { lastName: "name" }] },
        { filters: { agefilter: true } }
      );
await em.find(
        Membership,
        { $or: [{ role: "admin" }, { role: "moderator" }] },
        { filters: { userfilter: true } }
      );

select "e0".* from "user" as "e0" where ("e0"."first_name" = 'name' or "e0"."last_name" = 'name')

select "e0".* from "membership" as "e0" left join "user" as "e1" on "e0"."user_id" = "e1"."id" where ("e1"."first_name" = 'name' or "e1"."last_name" = 'name')

While wrapping a single $or statement in an unnecessary $and produces the wanted queries:

await em.find(
        User,
        { $and: [{ $or: [{ firstName: "name" }, { lastName: "name" }] }] },
        { filters: { agefilter: true } }
      );
 await em.find(
        Membership,
        { $and: [{ $or: [{ role: "admin" }, { role: "moderator" }] }] },
        { filters: { userfilter: true } }
      );

select "e0".* from "user" as "e0" where ("e0"."first_name" = 'name' or "e0"."last_name" = 'name') and ("e0"."age" = 18 or "e0"."age" = 21)

select "e0".* from "membership" as "e0" left join "user" as "e1" on "e0"."user_id" = "e1"."id" where ("e0"."role" = 'admin' or "e0"."role" = 'moderator') and ("e1"."first_name" = 'name' or "e1"."last_name" = 'name')

To Reproduce
I published a small repo with above test cases as a proof-of-concept.
https://github.com/NicolasDesmedt/mikro-orm-filter-issue

Expected behavior
All $or statements in find conditions and in @Filter statements should be conjugated in one query, without having to wrap one in an explicit $and statement.

Edit:

The issue isn't in @Filter , but in the FilterQuery conditions object when it's given multiple $or statements.

Doesn't work:

await em.find(Membership, { $or: [{ role: 'admin' }, { role: 'moderator' }], user: { $or: [{ firstName: 'John' }, { lastName: 'Doe' }] } })

select "e0".* from "membership" as "e0" left join "user" as "e1" on "e0"."user_id" = "e1"."id" where ("e1"."first_name" = 'John' or "e1"."last_name" = 'Doe')

Works:

await em.find(Membership, { $and: [{ $or: [{ role: 'admin' }, { role: 'moderator' }] }, { user: { $or: [{ firstName: 'John' }, { lastName: 'Doe' }] } }] });

select "e0".* from "membership" as "e0" left join "user" as "e1" on "e0"."user_id" = "e1"."id" where ("e0"."role" = 'admin' or "e0"."role" = 'moderator') and ("e1"."first_name" = 'John' or "e1"."last_name" = 'Doe')

[B4nan]

The issue is indeed with filters and how they are merged, with few more "child problems", I already got it fixed, but it had a lot of side effects, still having some tests failing.

Your edit might be another problem. With the filters the conditions were merged, but the values if both sides are defined were not merged, but rather replaced, so merging $or conditions were not working at all, the latest one was already there, the rest were overriden.

[B4nan]

4.5.5-dev.13