How do I recursively fetch tables?

Hi all, I have two tables in my postgres database:

person {
  id
}

person_to_person {
  id
  parent_id
  child_id
}

person {
  id
}

person_to_person {
  id
  parent_id
  child_id
}

The parent_id and child_id are references to the person table. I am trying to create a family tree-like structure, which means that I need to fetch every child recursively until there are no children left to fetch. Imagine a family tree. If you know the id of any given member in that tree (could be anywhere in the family tree), fetch all of its children recursively all the way to the bottom where there are no more children left. How do I accomplish this with Drizzle ORM? Preferably I want the result to be typed, so that the front-end can easily show this family tree on a webpage. I hope you can help me out!

4 Replies

Torbaz•7mo ago

Have a look into this, you will need to define the relations as shown under "Declaring relations" even if you already have the relations defined using .references() You will then be able to do db.query.person which the docs go into

Torbaz•7mo ago

https://orm.drizzle.team/docs/rqb

Drizzle ORM - Query

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

Sillvva•7mo ago

Using rqb would require you to manually define each nested relation. It's not recursive. If parent_id were a field on person, you could do self joins, but I'm not sure if it's possible to do recursive self joins with Drizzle without using the sql`` syntax almost entirely. https://stackoverflow.com/questions/1757260/simplest-way-to-do-a-recursive-self-join

Stack Overflow

Simplest way to do a recursive self-join?

What is the simplest way of doing a recursive self-join in SQL Server? PersonID | Initials | ParentID 1 CJ NULL 2 EB 1 3 MB 1 4 SW ...

Sillvva•7mo ago

Though yours is many to many, so self joins wouldn't work, but I think the principle is the same.

Gaming

Programming

How do I recursively fetch tables?