How to query deep nested relationships?

[mattiaz9]

Take this prisma example:

const realEstate = await prisma.realEstate.findFirstOrThrow({
  where: {
    id: 1
  },
  include: {
    units: {
      include: {
        owner: {
           include: {
              client: true
           }
        }
      }
    }
  }
})

With prisma is very easy to query nested relationships. In that example you have a one-to-many relationship with multiple units, which have a one-to-many relationship with a owner junction table which have a one-to-one relationship with a client.

Also there are other examples that can be complex, like nested relationships of the same table. In the following example I have a query to a client that have a one-to-many relationship with a proxy junction table that have a one-to-one relationship to again a client table.

const client = await prisma.client.findFirstOrThrow({
  where: {
    id: 1
  },
  include: {
    proxies: {
      include: {
        client: true
      }
    }
  }
})

In drizzle there are some examples of table joining, but they are very basic.

How can I achieve something like prisma does in drizzle?

BTW, yes I'm not good with database queries 😅

[dankochetov]

Such cases will be covered with a separate relational API layer. You can read this thread to get a better understanding: https://twitter.com/bloberenober/status/1644798759002357762?t=STLfv9ggkuIegcoy9dA51w&s=19

[dankochetov]

https://github.com/drizzle-team/drizzle-orm/blob/main/drizzle-orm/src/pg-core/README.md#join-aliases-and-self-joins

[paulwongx]

@mattiaz9 I'm a noob at SQL too, so just trying to help in light of no direct answer. If you're leftJoining, you want to connect another table to the first table. So the first leftJoin that references the second table by client.id === clientProxy.clientId is sufficient to connect the whole second clientProxy table.

If you want to connect TWO tables, the second leftJoin should reference another table.

So either

const result = await db
  .select()
  .from(clientProxy)
  .leftJoin(client, eq(client.id, clientProxy.clientId))
  .where(eq(clientProxy.id, 1))

OR

const result = await db
  .select()
  .from(clientProxy)
  .leftJoin(client, eq(client.id, clientProxy.clientId))
  .leftJoin(proxies, eq(clientProxy.clientId, proxies.id))
  .where(eq(clientProxy.id, 1))

I think.... here's some additional info on how to do multiple left joins in one SQL statement - in raw SQL
https://learnsql.com/blog/how-to-left-join-multiple-tables/

[mattiaz9]

@paulwongx yes, that's how I'm doing.
The main issue that I had was with data aggregation. I have to write custom aggregation function for every query now which is not nice, nor convenient so I asked if maybe there was some convenience api that was doing to work for me, but I guess that's not the aim of drizzle.

Anyway, the alias function is actually very useful, so thanks @dankochetov :)

[dankochetov]

Relational query builder is actually what I'm currently working on! It will address such issues in particular. Tracking issue: #504

[dankochetov]

Otherwise, you can always write your own aggregating function - and yes, we recognize that it might take some effort for deeply nested queries. Example here: https://github.com/drizzle-team/drizzle-orm/blob/main/docs/joins.md#aggregating-results