DT
Drizzle TeamNoahh

Duplicate relations when using `with`

I'm running into a problem where some duplicate values are being returned through a relation with with. Basically, I'm getting 
[
   {
      "id":"317078bd-6265-4156-986c-085bdf297765",
      "modifierGroups":[
         {
            "menuItemId":"317078bd-6265-4156-986c-085bdf297765",
            "modifierGroupId":"0fd458f3-128a-4787-8253-4d288efcf7c4",
            "order":0,
            "modifierGroup":{
               "id":"0fd458f3-128a-4787-8253-4d288efcf7c4",
               "modifiers":[
                  {
                     "modifierGroupId":"0fd458f3-128a-4787-8253-4d288efcf7c4",
                     "modifierId":"6e43c680-7c41-4840-9ff8-2597d98257ed",
                     "order":0,
                     "modifier":{
                        "id":"6e43c680-7c41-4840-9ff8-2597d98257ed",
                        "ingredient":{
                           "id":"8bb2caab-b758-4cb7-9012-f9e543cf4eb4"
                        },
                        "item":null
                     }
                  }
               ]
            }
         },
         {
            "menuItemId":"317078bd-6265-4156-986c-085bdf297765",
            "modifierGroupId":"0fd458f3-128a-4787-8253-4d288efcf7c4",
            "order":0,
            "modifierGroup":{
               "id":"0fd458f3-128a-4787-8253-4d288efcf7c4",
               "modifiers":[
                  {
                     "modifierGroupId":"0fd458f3-128a-4787-8253-4d288efcf7c4",
                     "modifierId":"6e43c680-7c41-4840-9ff8-2597d98257ed",
                     "order":0,
                     "modifier":{
                        "id":"6e43c680-7c41-4840-9ff8-2597d98257ed",
                        "ingredient":{
                           "id":"8bb2caab-b758-4cb7-9012-f9e543cf4eb4"
                        },
                        "item":null
                     }
                  }
               ]
            }
         }
      ]
   }
]
[
   {
      "id":"317078bd-6265-4156-986c-085bdf297765",
      "modifierGroups":[
         {
            "menuItemId":"317078bd-6265-4156-986c-085bdf297765",
            "modifierGroupId":"0fd458f3-128a-4787-8253-4d288efcf7c4",
            "order":0,
            "modifierGroup":{
               "id":"0fd458f3-128a-4787-8253-4d288efcf7c4",
               "modifiers":[
                  {
                     "modifierGroupId":"0fd458f3-128a-4787-8253-4d288efcf7c4",
                     "modifierId":"6e43c680-7c41-4840-9ff8-2597d98257ed",
                     "order":0,
                     "modifier":{
                        "id":"6e43c680-7c41-4840-9ff8-2597d98257ed",
                        "ingredient":{
                           "id":"8bb2caab-b758-4cb7-9012-f9e543cf4eb4"
                        },
                        "item":null
                     }
                  }
               ]
            }
         },
         {
            "menuItemId":"317078bd-6265-4156-986c-085bdf297765",
            "modifierGroupId":"0fd458f3-128a-4787-8253-4d288efcf7c4",
            "order":0,
            "modifierGroup":{
               "id":"0fd458f3-128a-4787-8253-4d288efcf7c4",
               "modifiers":[
                  {
                     "modifierGroupId":"0fd458f3-128a-4787-8253-4d288efcf7c4",
                     "modifierId":"6e43c680-7c41-4840-9ff8-2597d98257ed",
                     "order":0,
                     "modifier":{
                        "id":"6e43c680-7c41-4840-9ff8-2597d98257ed",
                        "ingredient":{
                           "id":"8bb2caab-b758-4cb7-9012-f9e543cf4eb4"
                        },
                        "item":null
                     }
                  }
               ]
            }
         }
      ]
   }
]
instead of 
[
   {
      "id":"317078bd-6265-4156-986c-085bdf297765",
      "modifierGroups":[
         {
            "menuItemId":"317078bd-6265-4156-986c-085bdf297765",
            "modifierGroupId":"0fd458f3-128a-4787-8253-4d288efcf7c4",
            "order":0,
            "modifierGroup":{
               "id":"0fd458f3-128a-4787-8253-4d288efcf7c4",
               "modifiers":[
                  {
                     "modifierGroupId":"0fd458f3-128a-4787-8253-4d288efcf7c4",
                     "modifierId":"6e43c680-7c41-4840-9ff8-2597d98257ed",
                     "order":0,
                     "modifier":{
                        "id":"6e43c680-7c41-4840-9ff8-2597d98257ed",
                        "ingredient":{
                           "id":"8bb2caab-b758-4cb7-9012-f9e543cf4eb4"
                        },
                        "item":null
                     }
                  }
               ]
            }
         }
      ]
   }
]
[
   {
      "id":"317078bd-6265-4156-986c-085bdf297765",
      "modifierGroups":[
         {
            "menuItemId":"317078bd-6265-4156-986c-085bdf297765",
            "modifierGroupId":"0fd458f3-128a-4787-8253-4d288efcf7c4",
            "order":0,
            "modifierGroup":{
               "id":"0fd458f3-128a-4787-8253-4d288efcf7c4",
               "modifiers":[
                  {
                     "modifierGroupId":"0fd458f3-128a-4787-8253-4d288efcf7c4",
                     "modifierId":"6e43c680-7c41-4840-9ff8-2597d98257ed",
                     "order":0,
                     "modifier":{
                        "id":"6e43c680-7c41-4840-9ff8-2597d98257ed",
                        "ingredient":{
                           "id":"8bb2caab-b758-4cb7-9012-f9e543cf4eb4"
                        },
                        "item":null
                     }
                  }
               ]
            }
         }
      ]
   }
]
You can see that all of the ids are duplicates. I know I ran into this plenty when I was doing my own joined queries, but am I doing something wrong here? My query is 
context.query.menuItems
    .findMany({
      with: {
        modifierGroups: {
          with: {
            modifierGroup: {
              with: {
                modifiers: {
                  with: {
                    modifier: {
                      with: {
                        ingredient: true,
                        item: true,
                      },
                    },
                  },
                  orderBy: modifierGroupModifiers.order,
                },
              },
            },
          },
          orderBy: menuItemModifierGroups.order,
        }
      },
    })
context.query.menuItems
    .findMany({
      with: {
        modifierGroups: {
          with: {
            modifierGroup: {
              with: {
                modifiers: {
                  with: {
                    modifier: {
                      with: {
                        ingredient: true,
                        item: true,
                      },
                    },
                  },
                  orderBy: modifierGroupModifiers.order,
                },
              },
            },
          },
          orderBy: menuItemModifierGroups.order,
        }
      },
    })
Any help is appreciated, I'm hoping to have this functional sometime tomorrow :) Thanks in advance!
AS
Andrii Sherman•359d ago
Try drizzle-orm@beta I guess @Dan Kochetov just fixed it there
AS
Andrii Sherman•359d ago
https://github.com/drizzle-team/drizzle-orm/issues/599
GitHub
[BUG]: multiple relations in with operator returns too many rows ...
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 ...
AS
Andrii Sherman•359d ago
I’ll add more tests today for this release and we will put it to latest
B
bloberenober•359d ago
yes, should be fixed in drizzle-orm@beta
N
Noahh•359d ago
Awesome, y'all are really making the world go round for me right now!
N
Noahh•359d ago
Hey, just a heads up, that same query on the beta branch works, except ingredient and item are just { id: "[" }
B
bloberenober•359d ago
welp will investigate further
N
Noahh•359d ago
Let me know if you want me to send my schema/relations, I'd be happy to
B
bloberenober•359d ago
yes please
AS
Andrii Sherman•359d ago
just wanted to ask you
N
Noahh•359d ago
I'm gonna remove some fields that I doubt have any relation to hopefully make it easier to read lol 
// schema.ts
export const menuItems = pgTable('menu_items', {
  id: uuid('id').defaultRandom().primaryKey()
});

export const modifierGroups = pgTable('modifier_groups', {
  id: uuid('id').defaultRandom().primaryKey()
});

export const menuItemModifierGroups = pgTable(
  'menu_item_modifier_groups',
  {
    menuItemId: uuid('menu_item_id')
      .notNull()
      .references(() => menuItems.id),
    modifierGroupId: uuid('modifier_group_id')
      .notNull()
      .references(() => modifierGroups.id),
    order: integer('order').default(0),
  },
  (table) => ({
    menuItemIdModifierGroupIdOrderPk: primaryKey(
      table.menuItemId,
      table.modifierGroupId,
      table.order,
    ),
  }),
);

export const ingredients = pgTable('ingredients', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text('name').notNull(),
  description: text('description'),
  imageUrl: text('image_url'),
  inStock: boolean('in_stock').default(true),
});

export const modifiers = pgTable('modifiers', {
  id: uuid('id').defaultRandom().primaryKey(),
  ingredientId: uuid('ingredient_id').references(() => ingredients.id),
  itemId: uuid('item_id').references(() => menuItems.id),
});

export const menuItemIngredients = pgTable(
  'menu_item_ingredients',
  {
    menuItemId: uuid('menu_item_id')
      .notNull()
      .references(() => menuItems.id),
    ingredientId: uuid('ingredient_id')
      .notNull()
      .references(() => ingredients.id),
    order: integer('order').default(0),
  },
  (table) => ({
    menuItemIdIngredientIdOrderPk: primaryKey(
      table.menuItemId,
      table.ingredientId,
      table.order,
    ),
  }),
);

export const modifierGroupModifiers = pgTable(
  'modifier_group_modifiers',
  {
    modifierGroupId: uuid('modifier_group_id')
      .notNull()
      .references(() => modifierGroups.id),
    modifierId: uuid('modifier_id')
      .notNull()
      .references(() => modifiers.id),
    order: integer('order').default(0),
  },
  (table) => ({
    modifierGroupIdModifierIdOrderPk: primaryKey(
      table.modifierGroupId,
      table.modifierId,
      table.order,
    ),
  }),
);
// schema.ts
export const menuItems = pgTable('menu_items', {
  id: uuid('id').defaultRandom().primaryKey()
});

export const modifierGroups = pgTable('modifier_groups', {
  id: uuid('id').defaultRandom().primaryKey()
});

export const menuItemModifierGroups = pgTable(
  'menu_item_modifier_groups',
  {
    menuItemId: uuid('menu_item_id')
      .notNull()
      .references(() => menuItems.id),
    modifierGroupId: uuid('modifier_group_id')
      .notNull()
      .references(() => modifierGroups.id),
    order: integer('order').default(0),
  },
  (table) => ({
    menuItemIdModifierGroupIdOrderPk: primaryKey(
      table.menuItemId,
      table.modifierGroupId,
      table.order,
    ),
  }),
);

export const ingredients = pgTable('ingredients', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: text('name').notNull(),
  description: text('description'),
  imageUrl: text('image_url'),
  inStock: boolean('in_stock').default(true),
});

export const modifiers = pgTable('modifiers', {
  id: uuid('id').defaultRandom().primaryKey(),
  ingredientId: uuid('ingredient_id').references(() => ingredients.id),
  itemId: uuid('item_id').references(() => menuItems.id),
});

export const menuItemIngredients = pgTable(
  'menu_item_ingredients',
  {
    menuItemId: uuid('menu_item_id')
      .notNull()
      .references(() => menuItems.id),
    ingredientId: uuid('ingredient_id')
      .notNull()
      .references(() => ingredients.id),
    order: integer('order').default(0),
  },
  (table) => ({
    menuItemIdIngredientIdOrderPk: primaryKey(
      table.menuItemId,
      table.ingredientId,
      table.order,
    ),
  }),
);

export const modifierGroupModifiers = pgTable(
  'modifier_group_modifiers',
  {
    modifierGroupId: uuid('modifier_group_id')
      .notNull()
      .references(() => modifierGroups.id),
    modifierId: uuid('modifier_id')
      .notNull()
      .references(() => modifiers.id),
    order: integer('order').default(0),
  },
  (table) => ({
    modifierGroupIdModifierIdOrderPk: primaryKey(
      table.modifierGroupId,
      table.modifierId,
      table.order,
    ),
  }),
);
// schema.ts (continued)
/* RELATIONS */
export const menuItemRelations = relations(menuItems, ({ one, many }) => ({
  ingredients: many(menuItemIngredients),
  modifierGroups: many(menuItemModifierGroups),
  category: one(menuCategories, {
    fields: [menuItems.categoryId],
    references: [menuCategories.id],
  }),
}));

export const menuItemIngredientRelations = relations(
  menuItemIngredients,
  ({ one }) => ({
    menuItem: one(menuItems, {
      fields: [menuItemIngredients.menuItemId],
      references: [menuItems.id],
    }),
    ingredient: one(ingredients, {
      fields: [menuItemIngredients.ingredientId],
      references: [ingredients.id],
    }),
  }),
);

export const ingredientRelations = relations(ingredients, ({ many }) => ({
  menuItems: many(menuItemIngredients),
}));

export const modifierGroupRelations = relations(modifierGroups, ({ many }) => ({
  menuItems: many(menuItemModifierGroups),
  modifiers: many(modifierGroupModifiers),
}));

export const modifierRelations = relations(modifiers, ({ one, many }) => ({
  modifierGroups: many(modifierGroupModifiers),
  ingredient: one(ingredients, {
    fields: [modifiers.ingredientId],
    references: [ingredients.id],
  }),
  item: one(menuItems, {
    fields: [modifiers.itemId],
    references: [menuItems.id],
  }),
}));

export const menuItemModifierGroupRelations = relations(
  menuItemModifierGroups,
  ({ one }) => ({
    menuItem: one(menuItems, {
      fields: [menuItemModifierGroups.menuItemId],
      references: [menuItems.id],
    }),
    modifierGroup: one(modifierGroups, {
      fields: [menuItemModifierGroups.modifierGroupId],
      references: [modifierGroups.id],
    }),
  }),
);

export const modifierGroupModifierRelations = relations(
  modifierGroupModifiers,
  ({ one }) => ({
    modifierGroup: one(modifierGroups, {
      fields: [modifierGroupModifiers.modifierGroupId],
      references: [modifierGroups.id],
    }),
    modifier: one(modifiers, {
      fields: [modifierGroupModifiers.modifierId],
      references: [modifiers.id],
    }),
  }),
);
// schema.ts (continued)
/* RELATIONS */
export const menuItemRelations = relations(menuItems, ({ one, many }) => ({
  ingredients: many(menuItemIngredients),
  modifierGroups: many(menuItemModifierGroups),
  category: one(menuCategories, {
    fields: [menuItems.categoryId],
    references: [menuCategories.id],
  }),
}));

export const menuItemIngredientRelations = relations(
  menuItemIngredients,
  ({ one }) => ({
    menuItem: one(menuItems, {
      fields: [menuItemIngredients.menuItemId],
      references: [menuItems.id],
    }),
    ingredient: one(ingredients, {
      fields: [menuItemIngredients.ingredientId],
      references: [ingredients.id],
    }),
  }),
);

export const ingredientRelations = relations(ingredients, ({ many }) => ({
  menuItems: many(menuItemIngredients),
}));

export const modifierGroupRelations = relations(modifierGroups, ({ many }) => ({
  menuItems: many(menuItemModifierGroups),
  modifiers: many(modifierGroupModifiers),
}));

export const modifierRelations = relations(modifiers, ({ one, many }) => ({
  modifierGroups: many(modifierGroupModifiers),
  ingredient: one(ingredients, {
    fields: [modifiers.ingredientId],
    references: [ingredients.id],
  }),
  item: one(menuItems, {
    fields: [modifiers.itemId],
    references: [menuItems.id],
  }),
}));

export const menuItemModifierGroupRelations = relations(
  menuItemModifierGroups,
  ({ one }) => ({
    menuItem: one(menuItems, {
      fields: [menuItemModifierGroups.menuItemId],
      references: [menuItems.id],
    }),
    modifierGroup: one(modifierGroups, {
      fields: [menuItemModifierGroups.modifierGroupId],
      references: [modifierGroups.id],
    }),
  }),
);

export const modifierGroupModifierRelations = relations(
  modifierGroupModifiers,
  ({ one }) => ({
    modifierGroup: one(modifierGroups, {
      fields: [modifierGroupModifiers.modifierGroupId],
      references: [modifierGroups.id],
    }),
    modifier: one(modifiers, {
      fields: [modifierGroupModifiers.modifierId],
      references: [modifiers.id],
    }),
  }),
);
Interesting, I kind of expected doing this to fix it, but it still happens with 
                        ingredient: {
                          columns: {
                            id: true,
                            name: true,
                            description: true,
                            imageUrl: true,
                            inStock: true,
                          },
                        },
                        ingredient: {
                          columns: {
                            id: true,
                            name: true,
                            description: true,
                            imageUrl: true,
                            inStock: true,
                          },
                        },
instead of 
ingredient: true
ingredient: true
Also, I'm not sure that this could be a cause but my identifiers are being truncated by Postgres. Only the ingredient and item are long enough, and they're still unique once they're truncated (only chops off a few letters).
AS
Andrii Sherman•358d ago
@Noahh 1 question I see this part of a query, while creating a repro test 
orderBy: modifierGroupModifiers.order,
orderBy: modifierGroupModifiers.order,
not sure it's a valid syntax for order Maybe you want to use 
orderBy: desc(modifierGroupModifiers.order),
// or
orderBy: asc(modifierGroupModifiers.order),
orderBy: desc(modifierGroupModifiers.order),
// or
orderBy: asc(modifierGroupModifiers.order),
cc @bloberenober I guess something we will need to handle? Or is it a valid syntax? I'll test both so we can know exactly if wrong orderBy was causing this issue
B
bloberenober•358d ago
if it doesn't raise a type error, then it's valid same as in SQL, if asc/desc is not specified, it uses asc
AS
Andrii Sherman•358d ago
yeah, i'm just new to this library still exploring
B
bloberenober•358d ago
no worries
N
Noahh•358d ago
yeah I figured it'd default. still probably a better idea to specify if for no other reason than legibility
B
bloberenober•358d ago
@Noahh the [ issue should now be fixed in drizzle-orm@beta, please re-test
N
Noahh•358d ago
Awesome, I will check it out in about an hour and let you know
Want results from more Discord servers?
Add your server
More Posts
Using BIN_TO_UUID / UUID_TO_BINI’m trying to understand the best way to use `BIN_TO_UUID` and `UUID_TO_BIN` (MySQL). The below is drizzle-kit drop config file does not existI have a `/foo/drizzle.config.ts` file as suggested in the docs (https://orm.drizzle.team/kit-docs/Related object is not typed correctlyI have the following schema ``` export const menus = pgTable('menus', { id: uuid('id').primaryKey(Custom vector type with pgvectorI'm trying to use the `pgvector` extension with a custom type but am running into an issue with the Missing 'with' clause additional operators (where, limit, offset, etc)Have been digging into the docs and the latest update. ❣️ In the docs (https://orm.drizzle.team/ddrizzle-zod type errors with latest versionsI updated all drizzle deps to latest and having type errors when using drizzle-zodI am confused on how the new relational queries worksIn the docs I see the following ``` import { pgTable, serial, text, integer, boolean } from 'drizzleis not assignable to type 'DrizzleD1Database'Hello This last release was amazing. Making joins simple was the missing piece for me. However, IDrizzle kit generate gives error after upgradeI have updated drizzle-orm to 0.26.0 and drizzle-kit to 018.0. I have defined relations according toIntrospection error with pgHello, I am trying to introspect my already created DB but when I run the cli command I get: ``` > dHow to declare PostgreSQL extensions/plugin?Reference: https://www.prisma.io/docs/concepts/components/prisma-schema/postgresql-extensions Is thHow to consume existing supabase migration?I have a supabase migrations and would like to reuse them as a starter for drizzle. When trying to pPrisma Studio style db explorerHey there! Anyone aware of a prisma studio style db-playground / db-admin-dashboard that works withIncorrect return type findFristHi! I just upgraded to `26.0` and I'm refactoring queries to `findFirst` where needed. But the retuRelations module - or conditionHi there! I have a teams table, an users table, and a team_members table that connects these two. AAre foreign key polyfills for PlanetScale supported by Drizzle?Hi Prisma supports this (although slow). See: https://www.prisma.io/docs/guides/database/planetscalTS error: Argument of type PgTableWithColumns is not assignable to parameter of type AnyPgTableHi all, after updating to latest i'm getting this TS error. This is my db creation and schema. ``` Many-to-many relational query issuesschema, relations, statements: https://gist.github.com/kylewardnz/37104f989807e96555ea856294a2b670 drizzle-kit: push wants to change column type that hasn't changedjust updated my dependencies: ``` dependencies: - drizzle-orm 0.25.4 + drizzle-orm 0.26.0 devDependedrizzle-kit doesn't seems picking up the default config TS (up:pg)Hi I'd like to report that the latest drizzle-kit `0.18.0` doesn't seems to detect my `drizzle.conf