Wrong query results when changing the order of the $and operations due to wrong alias mapping

[charmpitz]

Describe the bug
When using the find method on a repository changing the order of an $and children returns wrong results. See down below Query1 and Query2 that should provide identical results. Check the where conditions aliases and see e1.id, e1.name, e1.created_at for Query1 and e0.id, e0.name, e1.created_at for Query2 that are different. In Query1 it seems to be using only the tag table for filtering while in Query2 it uses both article and tag correctly.

Stack trace

Query 1:

{
    "$and": [
        { "tags": { "name": { "$in": ["tag1"] } } },
        { "id": { "$ne": 6 }, "createdAt": { "$gt": "2020-08-26T20:01:48.863Z" } }
    ]
}

transforms into:

select "e0".* from "blog"."article" as "e0" 
    left join "blog"."article_tag" as "e2" on "e0"."id" = "e2"."article_id" 
    left join "blog"."tag" as "e1" on "e2"."tag_id" = "e1"."id" 
where 
    "e1"."name" in ('tag1') and 
    "e1"."id" != 6 and 
    "e1"."created_at" > '2020-08-26T20:01:48.863Z' 
order by "e0"."created_at" asc limit 7

providing us with wrong results.

Query 2:

{
    "$and": [
        { "id": { "$ne": 6 }, "createdAt": { "$gt": "2020-08-26T20:01:48.863Z" } },
        { "tags": { "name": { "$in": ["tag1"] } } }
    ]
}

transforms into:

select "e0".* from "blog"."article" as "e0" 
    left join "blog"."article_tag" as "e2" on "e0"."id" = "e2"."article_id" 
    left join "blog"."tag" as "e1" on "e2"."tag_id" = "e1"."id" 
where 
    "e0"."id" != 6 and
    "e0"."created_at" > '2020-08-26T20:01:48.863Z' and
    "e1"."name" in ('tag1') 
order by "e0"."created_at" asc limit 7

providing us with expected results.

To Reproduce
Steps to reproduce the behavior:

1. Create 2 entities Article(id, createdAt), Tag(id, name, createdAt) and add a ManyToMany relationship between them.
2. Use the ArticleEntityRepository to run these 2 queries and see the difference.

Expected behavior
The expected behavior is to have the same result no matter the order of the operations.

Additional context

Versions

Dependency	Version
node	14.7.0
@mikro-orm/cli	^4.0.0-rc.4
@mikro-orm/core	^4.0.0-rc.4
@mikro-orm/migrations	^4.0.0-rc.4
@mikro-orm/nestjs	^4.0.0-alpha.3
@mikro-orm/postgresql	^4.0.0-rc.4
@mikro-orm/reflection	^4.0.0-rc.4
typescript	^3.7.4

[B4nan]

Confirmed. Btw the query is overcomplicated, there is no need for the explicit $and operator here. Without that it works as expected:

{
    "tags": { "name": { "$in": ["tag1"] } },
    "id": { "$ne": 6 }, 
    "createdAt": { "$gt": "2020-08-26T20:01:48.863Z" },
}

[charmpitz]

I agree but I'm working on a graphql endpoint that should support all versions of the query that's all. I've only given you a purely theoretical example above 😛 . Thanks for the fast response and all the hard work 👍 🥇