Filtering with multiple ANDs and ORs causing issues with Left Join

[keithintegral]

Hey, I'm just running a query and it's operating in unexpected ways. There are two entities involved: EntityA and EntityB (Fields and entity names changed for this issue). Below is the filter that I'm trying to execute.

{
   "organization":"orgId",
   "entities_b":{
      "organization":"orgId",
      "$and":[
         {
            "$or":[
               {
                  "amount::numeric":{
                     "$ne":0
                  }
               },
               {
                  "coalesce(fee_amount::numeric, 0)":{
                     "$ne":0
                  }
               }
            ]
         },
         {
            "fieldF":false,
            "fieldE":false
         },
         {
            "$and":[
               {
                  "$or":[
                     {
                        "fieldD":{
                           "$nin":[
                              2,
                              3
                           ]
                        }
                     },
                     {
                        "fieldD":null
                     }
                  ]
               },
               {
                  "$or":[
                     {
                        "fieldC":{
                           "$nin":[
                              2,
                              3
                           ]
                        }
                     },
                     {
                        "fieldC":null
                     }
                  ]
               }
            ]
         },
         {
            "$or":[
               {
                  "fieldB":{
                     "id":{
                        "$nin":[
                           "randomId1"
                        ]
                     }
                  }
               },
               {
                  "fieldB":null
               }
            ]
         },
         {
            "$or":[
               {
                  "fieldA":false
               }
            ]
         }
      ]
   }
}

It is executing using the query builder with the code below:

this.entityA
    .qb()
    .distinctOn(['timestamp', 'id'])
    .where(filter)
    // eslint-disable-next-line @typescript-eslint/no-non-null-assertion
    .orderBy(orderBy!)
    .limit(limit)
    .offset(offset)
    .setFlag(QueryFlag.DISABLE_PAGINATE)
    .getResult();

The debugger is building this query:

select 
  distinct on ("timestamp", "id") "t0".* 
from 
  "entities_a" as "t0" 
  left join "entities_b" as "t1" on "t0"."id" = "t1"."fieldID" 
where 
  "t0"."organization_id" = 'orgId' 
  and "t1"."organization_id" = 'orgId' 
  and (
    t1.amount :: numeric != 0 
    or coalesce(fee_amount :: numeric, 0) != 0
  ) 
  and "t1"."fieldE" = false 
  and "t1"."fieldF" = false 
  and (
    "t1"."fieldD" not in (2, 3) 
    or "t1"."fieldD" is null
  ) 
  and (
    "t1"."fieldC" not in (2, 3) 
    or "t1"."fieldC" is null
  ) 
  and (
    "t0"."fieldB" not in ('randomId1') 
    or "t1"."fieldB" is null
  ) 
  and "t1"."fieldA" = false 
order by 
  "t0"."timestamp" desc 
limit 
  50

It's throwing this error.

...limit 50 - column t0.fieldB does not exist

As you can see one of the fieldB columns is being mapped to EntityA (T0) when it should be mapped to EntityB (T1) - "t0"."fieldB" not in ('randomId1') . Any ideas on why this is happening? How I can fix it? If this is in fact a bug, let me know and I can move it over. But I want to confirm first.

I reproduced it here: https://github.com/keithintegral/reproduction/blob/6baa7281a602b17dd99245f28cc966a86c8ec8a7/src/example.test.ts#L152

• Postgres Driver
• Version: 5.9.7
• Node version: v21.4.0
• OS: Mac OSx 13.5.2 (22G91)

[B4nan]

Looks like the problem is caused by querying the PK instead of the FK:

{
  fieldB: {
-      id: {
-        $nin: [
-          'randomId1',
-        ],
-      },
+    $nin: [
+      'randomId1',
+    ],
  },
},

Btw you forgot to await the QB in your repro, so results is a promise and you serialize it so you get {} regardless of the actual query/result.

edit: also the nested $and placed inside a relation property condition is causing this, if I remove the top level $and (or the nested $or) it's also working fine (even without the change above)

[B4nan]

One note about the repro:

orm.em.create(EntityB, new EntityB('orgId', entityA, '100', 1, 1, new FieldB('anything')));

This does not make much sense, em.create is a method to create entity instance from DTO, and you are creating the entity instances by hand (via constructors). The call will run em.persist under the hood, so its effectively the same as this:

orm.em.persist(new EntityB('orgId', entityA, '100', 1, 1, new FieldB('anything')));

Or you could actually leverage what the method is for and pass in a DTO:

orm.em.create(EntityB, {
  organization: 'orgId',
  entityA: { organization: 'orgId' },
  amount: '100',
  fieldD: 1, 
  fieldC: 1, 
  fieldB: { name: 'anything' },
});

[keithintegral]

Thank you! That solved it 😀

[B4nan]

I'll try to address this at least in v6, I kinda see where the problem is but that part of the ORM is really complex, and fixing these issues is quite time-consuming.

[B4nan]

Found the culprit, it's the inlining of the payload done here which does not expect nesting of the payload.

https://github.com/mikro-orm/mikro-orm/blob/master/packages/knex/src/query/ObjectCriteriaNode.ts#L153-L166

[B4nan]

And here is a fix ebb966c