[BUG]: with Relation in findMany Returns Flattened Array Instead of Key-Value Object

[skmirajbn]

Report hasn't been filed before.

• I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.41.0

What version of drizzle-kit are you using?

0.30.6

Other packages

No response

Describe the Bug

When using Drizzle with findMany and the with clause to fetch related data, the returned result is not a structured key-value object as expected. Instead, it returns a flattened array of values for the related entities.

Here’s the query I’m running:

const query = db.query.scheduledTweetsTable.findMany({
  where: (scheduledTweetsTable, { inArray, eq }) => {
    const subquery = db
      .select({ id: agentsTable.id })
      .from(agentsTable)
      .where(eq(agentsTable.userId, Number(authUser.id)));
    return inArray(scheduledTweetsTable.agentId, subquery);
  },
  with: {
    agent: {
      with: {
        user: true,
      },
    },
  },
});

const data = (await db.execute(query as any)).rows[0].agent;

But the resulting data looks like this:

[
  13,
  '51290708-7ba7-416f-807a-b8bb4360dd8e',
  2,
  'Test AGent',
  'test',
  null,
  null,
  'paused',
  null,
  null,
  null,
  'aF9FTXhUOENjNdgsW5hSEpUTsdgEZ3N2I6MTpjaQ',
  'tgafnTsW1rBsgd2d2UaHFxxDxJsdgH0uN5APxFtfwIBtQaD8rIBbcFl',
  null,
  null,
  null,
  [
    2,
    'CUS2503221837dsg054152',
    'FYv6RwMBasadgsgTaPFLCb2FzN1JtwQknUsdgsdgxBgHocyy2Sh5Vv21',
    null,
    null,
    '',
    2,
    null,
    true,
    'CURRENT_TIMESTAMP',
    'CURRENT_TIMESTAMP'
  ]
]

I am building a package drizzle paginator here is the npm link https://www.npmjs.com/package/@skmirajbn/drizzle-paginator

For relation data got that flatten data. How to fix that.