Drizzle Team•2y ago

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!

18 Replies

Andrii Sherman•2y ago

Try drizzle-orm@beta I guess @Dan Kochetov just fixed it there

Andrii Sherman•2y 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 ...

Andrii Sherman•2y ago

I’ll add more tests today for this release and we will put it to latest

Dan•2y ago

yes, should be fixed in drizzle-orm@beta

NoahhOP•2y ago

Awesome, y'all are really making the world go round for me right now!

NoahhOP•2y ago

Hey, just a heads up, that same query on the beta branch works, except ingredient and item are just { id: "[" }

Dan•2y ago

welp will investigate further

NoahhOP•2y ago

Let me know if you want me to send my schema/relations, I'd be happy to

Dan•2y ago

yes please

Andrii Sherman•2y ago

just wanted to ask you

NoahhOP•2y 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).

Andrii Sherman•2y 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

Dan•2y 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

Andrii Sherman•2y ago

yeah, i'm just new to this library still exploring

Dan•2y ago

no worries

NoahhOP•2y ago

yeah I figured it'd default. still probably a better idea to specify if for no other reason than legibility

Dan•2y ago

@Noahh the [ issue should now be fixed in drizzle-orm@beta, please re-test

NoahhOP•2y ago

Awesome, I will check it out in about an hour and let you know

Gaming

Programming

Duplicate relations when using `with`