Self referencing table query

kajl_pl · 2024-07-15T13:54:41.415Z

So I have a `category` table that references itself, each category can have a parent that is also a category. All seems to be working fine, migration is being generated and works perfectly fine. But then when I try to query the data with references it fails on sql error: ```Internal error: Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select json_array(`CategoryTable_parent`.`id`, `CategoryTable_parent`.`paren...' at line 1``` Whole query: ```Query: select `CategoryTable`.`name`, `CategoryTable_parent`.`data` as `parent` from `category` `CategoryTable` left join lateral (select json_array(`CategoryTable_parent`.`id`, `CategoryTable_parent`.`parent_id`, `CategoryTable_parent`.`slug`, `CategoryTable_parent`.`name`, `CategoryTable_parent`.`image`, `CategoryTable_parent`.`description`) as `data` from (select * from `category` `CategoryTable_parent` where `CategoryTable_parent`.`id` = `CategoryTable`.`parent_id` limit ?) `CategoryTable_parent`) `CategoryTable_parent` on true -- params: [1]``` and the code used to query the data: ```db.query.CategoryTable.findMany({ with: { parent: true, }, })``` Any ideas if that is possible to work, or am I doing something wrong here?

R

rphlmr ⚡•7/15/24, 2:34 PM

this is an example with postgres: https://drizzle.run/ps9ydkhwlxzg9h7xehkgayp1

Drizzle Run

Self one-to-many relations - Drizzle Run

R

rphlmr ⚡•7/15/24, 2:35 PM

It seems that you are using MariaDB?

R

rphlmr ⚡•7/15/24, 2:35 PM

If so, sorry, it is not yet supported

https://github.com/drizzle-team/drizzle-orm/pull/1692

GitHub

Add support for Maria DB (core API implementation) by L-Mario564 · ...

(Partially) Addresses #203.
This PR aims to add support for Maria DB, which simply extends the existent MySQL dialect.
Tasks:

Implement mariadb driver.
Write core API integration tests for the m...

K

kajl_plOP•7/15/24, 3:15 PM

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

K

kajl_plOP•7/15/24, 3:15 PM

thanks for the help!

R

rphlmr ⚡•7/15/24, 3:18 PM

Postgres, the King :p

R

rphlmr ⚡•7/15/24, 3:19 PM

SQLite is fine too

R

rphlmr ⚡•7/15/24, 3:19 PM

https://turso.tech/ has made excellent contributions with PGLite (that enable SQLite for Drizzle Run)

Turso

Turso — SQLite for Production

Turso is SQLite for production: great developer experience and efficiency with essential quality of life improvements for modern applications.

K

kajl_plOP•7/15/24, 4:56 PM

hey @Raphaël M (@rphlmr)

one more question, is there any difference in using ". references" vs using the extraConfig?

R

rphlmr ⚡•7/15/24, 4:58 PM

No for simple case. Extra config is more powerful (can make compound keys)

K

kajl_plOP•7/15/24, 6:27 PM

Perfect, thank you!

K

kajl_plOP•7/16/24, 7:58 AM

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

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

with

with

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?

K

kajl_plOP•7/16/24, 7:58 AM

my second approach after your help was to do it the other way, to have a separate column and

many

many

many

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

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

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

K

kajl_plOP•7/16/24, 7:58 AM

not sure if that's a drizzle only question but maybe you'll be able to point me to a proper solution with drizzle

K

kajl_plOP•7/16/24, 7:59 AM

drizzle recursive pr: https://github.com/drizzle-team/drizzle-orm/pull/1405

GitHub

Feat: select without from and with recursive by Angelelz · Pull Req...

This will close #372 and will close #209. Also related to #1215?
This PR depends on #1218.
Upon merging, It will be possible to write a select statement without the .from() method. That will make p...

R

rphlmr ⚡•7/16/24, 11:54 AM

Hum I tried something but without this PR it is not that clean

R

rphlmr ⚡•7/16/24, 11:54 AM

https://drizzle.run/k1f7jokqsuspovfwah3d9ep6

Drizzle Run

Raw recursive query - Drizzle Run

K

kajl_plOP•7/16/24, 1:09 PM

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

K

kajl_plOP•7/16/24, 1:09 PM

will try to migrate when recursive lands

K

kajl_plOP•7/16/24, 1:10 PM

although typing the result of these

with: {
      children: {
        with: {
          child: {
            with: {
              children: {
                with: {
                  child: true,
                },
              },
            },
          },
        },
      },
    },

with: {
      children: {
        with: {
          child: {
            with: {
              children: {
                with: {
                  child: true,
                },
              },
            },
          },
        },
      },
    },

with: {
      children: {
        with: {
          child: {
            with: {
              children: {
                with: {
                  child: true,
                },
              },
            },
          },
        },
      },
    },

with: {
      children: {
        with: {
          child: {
            with: {
              children: {
                with: {
                  child: true,
                },
              },
            },
          },
        },
      },
    },

is a nightmare for me

Kkajl_pl although typing the result of these ```with: { children: { with: {...

R

rphlmr ⚡•7/16/24, 1:25 PM

At this point I would try a Awaited<ReturnType<typeof theQueryConst>> but I wonder if TS will survive

K

kajl_plOP•7/16/24, 1:26 PM

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;

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

R

rphlmr ⚡•7/16/24, 1:27 PM

Recursion is one of the few things I can’t get into

R

rphlmr ⚡•7/16/24, 1:28 PM

It crashed my brain. This and generator functions

K

kajl_plOP•7/16/24, 1:29 PM

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

R

rphlmr ⚡•7/16/24, 1:31 PM

Yeah sometimes I create types helper and later I can’t even guess why and how I did that.
Drizzle type system is

(even if it has some bugs), huge respect to the team

K

kajl_plOP•7/16/24, 1:34 PM

well hopefully one day I'll uinderstand some of these typings

other than that, you're a

thank you!

K

kajl_plOP•7/17/24, 9:46 AM

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

R

rphlmr ⚡•7/17/24, 10:26 AM

Wow nice! I am happy to read that it helped you, I built Drizzle Run to achieve this goal

T

Tibfib•8/14/24, 6:27 PM

@Raphaël M (@rphlmr)

if you look at the console log of this, it's actually snake casing the keys, besides manually camel-casing each key, is there a way to fix this?

T

Tibfib•8/14/24, 6:33 PM

and I think same issue, but it's also not running my custom column type processing

TTibfib @Raphaël M (@rphlmr) ⚡ if you look at the console log of this, it's actually sna...

R

rphlmr ⚡•8/14/24, 6:42 PM

For the snake_case it's

.as("parent_id")

.as("parent_id")

.as("parent_id")

.as("parent_id") ==>

.as("parentId")

.as("parentId")

.as("parentId")

.as("parentId")

T

Tibfib•8/14/24, 6:44 PM

if I add another column to the schema and try to reference it, it comes down in snake case https://drizzle.run/v7feo604avcvkfr5jz65km3u

Drizzle Run

(fork) Raw recursive query - Drizzle Run

T

Tibfib•8/14/24, 6:45 PM

R

rphlmr ⚡•8/14/24, 6:50 PM

Ah yes. It’s because we run a raw query, so we lose Drizzle’s auto mapping :/

T

Tibfib•8/14/24, 6:51 PM

dang, and I'm assuming there's no easy workarounds?

R

rphlmr ⚡•8/14/24, 6:53 PM

give me 15 min

R

rphlmr ⚡•8/14/24, 7:33 PM

https://drizzle.run/qck4zcejezajmiljgsnuz9df

Drizzle Run

(fork) (fork) Raw recursive query - Drizzle Run

R

rphlmr ⚡•8/14/24, 7:36 PM

the alternative way (manual)

    otherVariable: sql`${CategoryTable.otherVariable}`
      .mapWith(CategoryTable.otherVariable)
      .as("otherVariable"),

    otherVariable: sql`${CategoryTable.otherVariable}`
      .mapWith(CategoryTable.otherVariable)
      .as("otherVariable"),

    otherVariable: sql`${CategoryTable.otherVariable}`
      .mapWith(CategoryTable.otherVariable)
      .as("otherVariable"),

    otherVariable: sql`${CategoryTable.otherVariable}`
      .mapWith(CategoryTable.otherVariable)
      .as("otherVariable"),

R

rphlmr ⚡•8/14/24, 7:37 PM

The only query that needs that is the referent query (left side of the unionAll)

T

Tibfib•8/14/24, 8:58 PM

@Raphaël M (@rphlmr)

Wow, this is great and very helpful for the camelCase issue!

Unfortunately, there's just too much I'm losing with the "raw" to work around beyond just the camelCase issues. I've got custom types not running, I've got a

bigserial

bigserial

bigserial

bigserial +

bigint

bigint

bigint

bigint columns coming in as strings, etc. Is there a way to manually convert each record using a table's columns definition? That would be a great stopgap solution until $withRecursive comes out

T

Tibfib•8/14/24, 9:36 PM

I think I'll just do a manual with recursive query with

sql

sql

sql

sql to get the ids, then use the query api to get the actual items. I think my size will permit it. Then I'll swap out with a single recursive query when it's ready