Describe the bug

I found another issue with composite keys 😅

When I load an entity that has a filter configured and then separately try to populate one of its 1:m relationships using the entity.collection.load()/em.populate(entity, ['collection']) methods, the entity's primary key is added to the join clause in addition to the overall where clause. I observed this behaviour in the transition from 6.4.15->6.4.16, but is still present in the latest 6.5.2.

const workspace = await orm.em.findOneOrFail(Workspace, {
  org: 1,
  id: 10,
});

const users = await workspace.users.load();

Generates the following SQL:

select `u0`.*,
    `w1`.`org_id` as `w1__org_id`,
    `w1`.`id` as `w1__id`
from `user` as `u0`
    inner join `workspace` as `w1` on `u0`.`org_id` = `w1`.`org_id`
    and `u0`.`workspace_id` = `w1`.`id`
    and (`w1`.`org_id`, `w1`.`id`) in (1, 10) -- ERROR
    and `w1`.`deleted_at` is null
where (`w1`.`org_id`, `w1`.`id`) in ( values (1, 10))
-- SQLITE_ERROR: IN(...) element has 1 term - expected 2

The condition should instead wrap the composite key in an additional set of parentheses (e.g. and (`w1`.`org_id`, `w1`.`id`) in ((1, 10)))

Is this additional condition in the JOIN intended behaviour? If so, I have a fix for this which wraps the value in an extra set of parentheses if it's an array, I'm happy to open a PR for it.

diff --git a/packages/knex/src/query/QueryBuilderHelper.ts b/packages/knex/src/query/QueryBuilderHelper.ts
index a045154ec..bb0e75da2 100644
--- a/packages/knex/src/query/QueryBuilderHelper.ts
+++ b/packages/knex/src/query/QueryBuilderHelper.ts
@@ -380,7 +380,7 @@ export class QueryBuilderHelper {
 
     if (['$in', '$nin'].includes(operator) && Array.isArray(value)) {
       params.push(...value as Knex.Value[]);
-      return `${this.knex.ref(column)} ${replacement} (${value.map(() => '?').join(', ')})`;
+      return `${this.knex.ref(column)} ${replacement} (${value.map(v => Array.isArray(v) ? '(?)' : '?').join(', ')})`;
     }
 
     if (operator === '$exists') {

Reproduction

https://github.com/jaichinger/mikro-orm-reproduction-4

What driver are you using?

@mikro-orm/sqlite

MikroORM version

6.5.2

Node.js version

22

Operating system

macOS

Validations

• Read the Contributing Guidelines.
• Read the docs.
• Check that there isn't already an issue that reports the same bug to avoid creating a duplicate.
• Check that this is a concrete bug. For Q&A open a GitHub Discussion or join our Discord.
• The provided reproduction is a minimal reproducible example of the bug.