kajl_pl
kajl_pl
PostsComments
DTDrizzle Team
Created by kajl_pl on 7/25/2024 in #help
Internal error: TypeError: Cannot read properties of undefined (reading 'referencedTable')`
oh ffs... restarted project and now it works 🤦‍♂️
3 replies
DTDrizzle Team
Created by kajl_pl on 7/15/2024 in #help
Self referencing table query
hey @Raphaël M (@rphlmr) ⚡ just FYI, thanks to your example and some googling I just understood how recursive query works, and even been able to write my own that takes current category and lists all its the the parents recursively 😅
43 replies
DTDrizzle Team
Created by kajl_pl on 7/15/2024 in #help
Self referencing table query
well hopefully one day I'll uinderstand some of these typings 😅 other than that, you're a ⭐ thank you!
43 replies
DTDrizzle Team
Created by kajl_pl on 7/15/2024 in #help
Self referencing table query
well TS is great, but I have no idea how people do come up with all these typings, that's madness, then recursion on itself is easy 🤣
43 replies
DTDrizzle Team
Created by kajl_pl on 7/15/2024 in #help
Self referencing table query
well for now I did this manually 🤣 
export type Category = typeof CategoryTable.$inferSelect;

export type CategorySub = {
  categoryId: number;
  childId: number;
  category: CategoryChildren;
};

export type CategoryChildren = Category & {
  children: CategorySub[];
};

export type CategoryWithChildren = CategoryChildren | undefined;
export type Category = typeof CategoryTable.$inferSelect;

export type CategorySub = {
  categoryId: number;
  childId: number;
  category: CategoryChildren;
};

export type CategoryChildren = Category & {
  children: CategorySub[];
};

export type CategoryWithChildren = CategoryChildren | undefined;
works ok'ish
43 replies
DTDrizzle Team
Created by kajl_pl on 7/15/2024 in #help
Self referencing table query
although typing the result of these 
with: {
      children: {
        with: {
          child: {
            with: {
              children: {
                with: {
                  child: true,
                },
              },
            },
          },
        },
      },
    },
with: {
      children: {
        with: {
          child: {
            with: {
              children: {
                with: {
                  child: true,
                },
              },
            },
          },
        },
      },
    },
is a nightmare for me 🤨
43 replies
DTDrizzle Team
Created by kajl_pl on 7/15/2024 in #help
Self referencing table query
will try to migrate when recursive lands
43 replies
DTDrizzle Team
Created by kajl_pl on 7/15/2024 in #help
Self referencing table query
well again thank you! although that is such a madness that I'll just stick to the nested with solution posted above, as at least I understand what's going on 🤣
43 replies
DTDrizzle Team
Created by kajl_pl on 7/15/2024 in #help
Self referencing table query
drizzle recursive pr: https://github.com/drizzle-team/drizzle-orm/pull/1405
43 replies
DTDrizzle Team
Created by kajl_pl on 7/15/2024 in #help
Self referencing table query
not sure if that's a drizzle only question but maybe you'll be able to point me to a proper solution with drizzle
43 replies
DTDrizzle Team
Created by kajl_pl on 7/15/2024 in #help
Self referencing table query
my second approach after your help was to do it the other way, to have a separate column and many relation, but that's more complicated: 
export const CategoryTable = pgTable('category', {
  id: serial('id').primaryKey().unique(),
  slug: varchar('slug', { length: 128 }).notNull().unique(),
  name: varchar('name', { length: 128 }).notNull().unique(),
  image: varchar('image', { length: 1024 }).notNull(),
  description: text('description').notNull(),
});

export const CategoryRelations = relations(CategoryTable, ({ many }) => ({
  children: many(CategoryToChildTable, { relationName: 'parent' }),
}));

export const CategoryToChildTable = pgTable(
  'category_to_child',
  {
    categoryId: integer('category_id')
      .notNull()
      .references(() => CategoryTable.id),
    childId: integer('child_id')
      .notNull()
      .references(() => CategoryTable.id),
  },
  (tbl) => ({
    pk: primaryKey({ columns: [tbl.categoryId, tbl.childId] }),
  }),
);

export const CategoryToChildRelations = relations(
  CategoryToChildTable,
  ({ one }) => ({
    parent: one(CategoryTable, {
      relationName: 'parent',
      fields: [CategoryToChildTable.categoryId],
      references: [CategoryTable.id],
    }),
    child: one(CategoryTable, {
      relationName: 'child',
      fields: [CategoryToChildTable.childId],
      references: [CategoryTable.id],
    }),
  }),
);
export const CategoryTable = pgTable('category', {
  id: serial('id').primaryKey().unique(),
  slug: varchar('slug', { length: 128 }).notNull().unique(),
  name: varchar('name', { length: 128 }).notNull().unique(),
  image: varchar('image', { length: 1024 }).notNull(),
  description: text('description').notNull(),
});

export const CategoryRelations = relations(CategoryTable, ({ many }) => ({
  children: many(CategoryToChildTable, { relationName: 'parent' }),
}));

export const CategoryToChildTable = pgTable(
  'category_to_child',
  {
    categoryId: integer('category_id')
      .notNull()
      .references(() => CategoryTable.id),
    childId: integer('child_id')
      .notNull()
      .references(() => CategoryTable.id),
  },
  (tbl) => ({
    pk: primaryKey({ columns: [tbl.categoryId, tbl.childId] }),
  }),
);

export const CategoryToChildRelations = relations(
  CategoryToChildTable,
  ({ one }) => ({
    parent: one(CategoryTable, {
      relationName: 'parent',
      fields: [CategoryToChildTable.categoryId],
      references: [CategoryTable.id],
    }),
    child: one(CategoryTable, {
      relationName: 'child',
      fields: [CategoryToChildTable.childId],
      references: [CategoryTable.id],
    }),
  }),
);
that can be queried with: 
db.query.CategoryTable.findMany({
    where: eq(CategoryTable.slug, ''),
    with: {
      children: {
        with: {
          child: {
            with: {
              children: {
                with: {
                  child: true,
                },
              },
            },
          },
        },
      },
    },
  });
db.query.CategoryTable.findMany({
    where: eq(CategoryTable.slug, ''),
    with: {
      children: {
        with: {
          child: {
            with: {
              children: {
                with: {
                  child: true,
                },
              },
            },
          },
        },
      },
    },
  });
43 replies
DTDrizzle Team
Created by kajl_pl on 7/15/2024 in #help
Self referencing table query
hey @Raphaël M (@rphlmr) ⚡ me again 😅 so as I mentiond at the beginning, what I was tring to achieve was to have CATEGORIES table for a samll shop, where each category can have 1 parent or null (top category) then what I would need is to have a way to query all of them in a tree like structure, top to bottom, which seems to be done in sql with "recursive" but that's still not yet available in drizzle and if I have: 
export const CategoryTable = pgTable(
  'category',
  {
    id: serial('id').primaryKey().unique(),
    parent_id: integer('parent_id').references(
      (): AnyPgColumn => CategoryTable.id,
    ),
    slug: varchar('slug', { length: 128 }).notNull().unique(),
    name: varchar('name', { length: 128 }).notNull().unique(),
    image: varchar('image', { length: 1024 }).notNull(),
    description: text('description').notNull(),
  },
  (table) => {
    return {
      slug_idx: index('slug_idx').on(table.slug),
    };
  },
);

export const CategoryRelations = relations(CategoryTable, ({ one }) => ({
  parent: one(CategoryTable, {
    fields: [CategoryTable.parent_id],
    references: [CategoryTable.id],
  }),
}));
export const CategoryTable = pgTable(
  'category',
  {
    id: serial('id').primaryKey().unique(),
    parent_id: integer('parent_id').references(
      (): AnyPgColumn => CategoryTable.id,
    ),
    slug: varchar('slug', { length: 128 }).notNull().unique(),
    name: varchar('name', { length: 128 }).notNull().unique(),
    image: varchar('image', { length: 1024 }).notNull(),
    description: text('description').notNull(),
  },
  (table) => {
    return {
      slug_idx: index('slug_idx').on(table.slug),
    };
  },
);

export const CategoryRelations = relations(CategoryTable, ({ one }) => ({
  parent: one(CategoryTable, {
    fields: [CategoryTable.parent_id],
    references: [CategoryTable.id],
  }),
}));
I can query everything using with but then it goes bottom to top, so the other way around is there a way to solve that in drizzle at the moment?
43 replies
DTDrizzle Team
Created by kajl_pl on 7/15/2024 in #help
Self referencing table query
Perfect, thank you!
43 replies
DTDrizzle Team
Created by kajl_pl on 7/15/2024 in #help
Self referencing table query
hey @Raphaël M (@rphlmr) ⚡ one more question, is there any difference in using ". references" vs using the extraConfig?
43 replies
DTDrizzle Team
Created by kajl_pl on 7/15/2024 in #help
Self referencing table query
thanks for the help!
43 replies
DTDrizzle Team
Created by kajl_pl on 7/15/2024 in #help
Self referencing table query
ahh that makes sense, seems I'll just migrate to postgres as seems there's many more tutorials for it, and at that point it doesn't make any difference for me
43 replies