[BUG]: does nesting with blocks has threshold with pg

[nagy-nabil]

What version of drizzle-orm are you using?

^0.28.6

What version of drizzle-kit are you using?

^0.19.13

Describe the Bug

once the nesting of with go behind some threshold client through error the column doesn't exists, I will provide example to better explain the problem

I have many to many relation between the following [(roles, permissions), (roles, users), (roles, groups), (groups, users)] I want to get user permissions(from roles/groups)

here's the query, the error and what I tried is in comment

    const user = await this.db.query.users.findFirst({
      columns: {
        password: false,
      },
      where: eq(users.id, userId),
      with: {
        usersToGroups: {
          with: {
            group: {
              with: {
                rolesToGroups: {
                  with: {
                    // NOTE: this part is the same as In usersToRoles, and it doesn't throw error
                    role: {
                      with: {
                        permissionsToRoles: {
                          with: {
                            permission: true, // it throws error here error: column users_usersToGroups_group_rolesToGroups_role_permissionsToRoles.permission_id does not exist
                            // backend:dev:     at D:\collage\grad\frontend\node_modules\.pnpm\pg@8.11.3\node_modules\pg\lib\client.js:526:17
                            // but once i remove this last with to only `permissionsToRoles: true` works fine without errors, that's why I'm suggesting error has to do with nesting `with` 
                          },
                        },
                      },
                    },
                  },
                },
              },
            },
          },
        },
        usersToRoles: {
          with: {
            role: {
              with: {
                permissionsToRoles: {
                  with: {
                    permission: true,
                  },
                },
              },
            },
          },
        },
      },
    });

Expected behavior

You can chain nested with statements as much as necessary.

Environment & setup

import { Client } from 'pg';

export type DatabaseI = NodePgDatabase<typeof schema>;

  const client = new Client({
    connectionString: connectionString,
  });
  await client.connect();
  const db: DatabaseI = drizzle(client, { schema });

[noobmaster19]

Same issue did u find a fix?

[nagy-nabil]

Same issue did u find a fix?

i just divided the query into multiple steps