Self referencing table query

So I have a

category

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?

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

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

It seems that you are using MariaDB?

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

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

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

thanks for the help!

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

Postgres, the King :p

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

SQLite is fine too

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.

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?

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

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

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

Perfect, thank you!

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:

I can query everything using

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?

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 relation, but that's more complicated:

that can be queried with:

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

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

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

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

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

https://drizzle.run/k1f7jokqsuspovfwah3d9ep6

Drizzle Run

Raw recursive query - Drizzle Run

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

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

will try to migrate when recursive lands

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

although typing the result of these

is a nightmare for me

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

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

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

well for now I did this manually

works ok'ish

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

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

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

It crashed my brain. This and generator functions

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

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

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!

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

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

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?

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

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

For the snake_case it's ==>

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

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

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

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

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

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

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

give me 15 min

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

https://drizzle.run/qck4zcejezajmiljgsnuz9df

Drizzle Run

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

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

the alternative way (manual)

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

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

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

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

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

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