Drizzle Team•6mo ago

Self referencing table query

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

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]

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

db.query.CategoryTable.findMany({
    with: {
      parent: true,
    },
  })

Any ideas if that is possible to work, or am I doing something wrong here?

31 Replies

rphlmr ⚡•6mo ago

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

Drizzle Run

Self one-to-many relations - Drizzle Run

rphlmr ⚡•6mo ago

It seems that you are using MariaDB?

rphlmr ⚡•6mo ago

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

kajl_plOP•6mo ago

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 thanks for the help!

rphlmr ⚡•6mo ago

Postgres, the King :p SQLite is fine too

rphlmr ⚡•6mo ago

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.

kajl_plOP•6mo ago

hey @Raphaël M (@rphlmr) ⚡ one more question, is there any difference in using ". references" vs using the extraConfig?

rphlmr ⚡•6mo ago

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

kajl_plOP•6mo ago

Perfect, thank you! 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? 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,
                },
              },
            },
          },
        },
      },
    },
  });

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

kajl_plOP•6mo ago

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

rphlmr ⚡•6mo ago

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

rphlmr ⚡•6mo ago

https://drizzle.run/k1f7jokqsuspovfwah3d9ep6

Drizzle Run

Raw recursive query - Drizzle Run

kajl_plOP•6mo ago

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 🤣 will try to migrate when recursive lands 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 🤨

rphlmr ⚡•6mo ago

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

kajl_plOP•6mo ago

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

rphlmr ⚡•6mo ago

Recursion is one of the few things I can’t get into 😅 It crashed my brain. This and generator functions 😆

kajl_plOP•6mo ago

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 🤣

rphlmr ⚡•6mo ago

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 😮

kajl_plOP•6mo ago

well hopefully one day I'll uinderstand some of these typings 😅 other than that, you're a ⭐ thank you! 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 😅

rphlmr ⚡•6mo ago

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

Tibfib•5mo ago

@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?

Tibfib•5mo ago

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

rphlmr ⚡•5mo ago

For the snake_case it's .as("parent_id") ==> .as("parentId")

Tibfib•5mo ago

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

Tibfib•5mo ago

rphlmr ⚡•5mo ago

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

Tibfib•5mo ago

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

rphlmr ⚡•5mo ago

give me 15 min

rphlmr ⚡•5mo ago

https://drizzle.run/qck4zcejezajmiljgsnuz9df

Drizzle Run

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

rphlmr ⚡•5mo ago

the alternative way (manual)

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

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

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

Tibfib•5mo ago

@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 +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 I think I'll just do a manual with recursive query with 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

Gaming

Programming

Self referencing table query