[BUG]: multiple relations in with operator returns too many rows

[twobit]

What version of drizzle-orm are you using?

0.26.0

What version of drizzle-kit are you using?

0.18/0

Describe the Bug

Using the schema below and a query with multiple relations in the with operator seems to explode the amount of rows in each relation:

db.query.tenant.findFirst({
    where: eq(tenant.id, '...'),
    with: {
      users: true,
      projects: true
    }
  });

Schema:

// schema.ts
import { InferModel, relations } from 'drizzle-orm';
import { date, integer, pgTable, text, timestamp, uuid} from 'drizzle-orm/pg-core';

// Tenant
export const tenant = pgTable('tenant', {
  id: uuid('id').primaryKey().notNull().defaultRandom(),
  name: text('name').notNull(),
});

export const tenantRelations = relations(tenant, ({ many }) => ({
  projects: many(project),
  users: many(user),
}));

// Project
export const project = pgTable('project', {
  id: uuid('id').primaryKey().notNull().defaultRandom(),
  tenant_id: uuid('tenant_id').notNull().references(() => tenant.id),
});

export const projectRelations = relations(project, ({ one }) => ({
  tenant: one(tenant, { fields: [project.tenant_id], references: [tenant.id] }),
}));

// User
export const user = pgTable('user', {
  id: uuid('id').primaryKey().notNull().defaultRandom(),
  tenant_id: uuid('tenant_id').notNull().references(() => tenant.id),
});

export const userRelations = relations(user, ({ one }) => ({
  tenant: one(user, { fields: [user.tenant_id], references: [tenant.id] }),
}));


### Expected behavior

_No response_

### Environment & setup

_No response_

[dankochetov]

Could you elaborate? What does "explode the amount of rows" mean? What's the expected and actual behavior?

[kylekz]

I'm also getting this issue. I've posted in the discord, but here's my schema: https://gist.github.com/kylewardnz/37104f989807e96555ea856294a2b670

In the database, I have 1 artist, then 2 members linked to the artist via pivot table, and 1 album.

This is simplified but what I'm expecting is a structure like so:

{
  "id": 1,
  "name": "Artist 1",
  "members": [
    {
      "id": 1,
      "artistId": 1,
      "memberId": 1,
      "member": {
        "id": 1,
        "name": "Member 1"
      }
    },
    {
      "id": 2,
      "artistId": 1,
      "memberId": 2,
      "member": {
        "id": 2,
        "name": "Member 2"
      }
    }
  ],
  "albums": [
    {
      "id": 1,
      "name": "Album 1"
    }
  ]
}

With this query here...

await db.query.artists.findFirst({
  where: (artists, { eq }) => eq(artists.id, 1),
  with: {
    albums: true,
    members: {
      with: {
        member: true,
      }
    },
  },
})

...I'm getting a result like so:

{
  "id": 1,
  "name": "Artist 1",
  "members": [
    {
      "id": 1,
      "artistId": 1,
      "memberId": 1,
      "member": {
        "id": 1,
        "name": "Member 1"
      }
    },
    {
      "id": 2,
      "artistId": 1,
      "memberId": 2,
      "member": {
        "id": 2,
        "name": "Member 2"
      }
    }
  ],
  "albums": [
    {
      "id": 1,
      "name": "Album 1"
    },
    {
      "id": 1,
      "name": "Album 1"
    }
  ]
}

If I create a new member and link it to the artist, a third copy of the album with id=1 appears in the albums array.

If I remove the members relation from the with object, then only one album is returned, as expected.

The inverse also happens. If I have albums 1 & 2 but only member 1, then two copies of member 1 appear in the members array.

[AndriiSherman]

Thanks a lot for the detailed example!

[dankochetov]

@twobit @kylewardnz check out drizzle-orm@beta, should be fixed there.

[twobit]

Thanks @dankochetov, was the beta branch published? I'm not seeing the change has landed yet

[kylekz]

A quick test on the query I described seems to work properly now, will test a bit more thoroughly later this evening. Thanks @dankochetov!

[dankochetov]

Thanks @dankochetov, was the beta branch published? I'm not seeing the change has landed yet

it's published as drizzle-orm@beta @twobit

[justinikeako]

Ran into this bug yesterday while fully migrating one of my side projects from Prisma. Just tested the beta, and the query that was giving me problems now works flawlessly.

The devil works hard, but the drizzle team works harder 🫡

[twobit]

@dankochetov looks like I also needed to upgrade drizzle-zod@beta, I was getting an old version in my npm dependency tree despite updating to drizzle-orm@beta. The fix looks good! Thank you

[johanneskares]

I think this change broke another functionality. This example which previously worked is now broken:

const result = await drizzle.query.asset.findMany({
  with: {
    userAssetDrop: {
      columns: {},
      where: (table) => and(eq(table.userId, input.userId)),
    },
    talent: {
      columns: { id: true, username: true },
    },
  },
  where: (table) => sql`json_array_length(${table.userAssetDrop}) > 0`,
});

I'm getting the error: function json_array_length(text) does not exist

As soon as I remove the talent, it starts working gain. Also worked previously with drizzle 0.26.0

@dankochetov

[johanneskares]

fyi, created a new issue: #686