Drizzle Team

help

Duplicate relations when using `with`

NNoahh

5/22/2023

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
                     }
                  }
               ]
            }
         }
      ]
   }
]

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
                     }
                  }
               ]
            }
         }
      ]
   }
]

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,
        }
      },
    })

Any help is appreciated, I'm hoping to have this functional sometime tomorrow :) Thanks in advance!

ASAndrii Sherman

5/22/2023

Try drizzle-orm@beta

ASAndrii Sherman

5/22/2023

I guess @Dan Kochetov just fixed it there

ASAndrii Sherman

5/22/2023

https://github.com/drizzle-team/drizzle-orm/issues/599

ASAndrii Sherman

5/22/2023

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

Bbloberenober

5/22/2023

yes, should be fixed in drizzle-orm@beta

NNoahh

5/22/2023

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

NNoahh

5/22/2023

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

Bbloberenober

5/22/2023

welp

Bbloberenober

5/22/2023

will investigate further

NNoahh

5/22/2023

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

Bbloberenober

5/22/2023

yes please

ASAndrii Sherman

5/22/2023

just wanted to ask you

NNoahh

5/22/2023

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,
    ),
  }),
);

NNoahh

5/22/2023

// 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],
    }),
  }),
);

NNoahh

5/22/2023

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,
                          },
                        },

instead of

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).

ASAndrii Sherman

5/23/2023

@Noahh 1 question

I see this part of a query, while creating a repro test

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),

cc @bloberenober I guess something we will need to handle? Or is it a valid syntax?

ASAndrii Sherman

5/23/2023

I'll test both so we can know exactly if wrong orderBy was causing this issue

Bbloberenober

5/23/2023

if it doesn't raise a type error, then it's valid

Bbloberenober

5/23/2023

same as in SQL, if asc/desc is not specified, it uses asc

ASAndrii Sherman

5/23/2023

yeah, i'm just new to this library

ASAndrii Sherman

5/23/2023

still exploring

Bbloberenober

5/23/2023

no worries

NNoahh

5/23/2023

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

Bbloberenober

5/23/2023

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

NNoahh

5/23/2023

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