[BUG]: Deeply nested query when there's a reverse query at the end

[noobmaster19]

What version of drizzle-orm are you using?

0.31.2

What version of drizzle-kit are you using?

0.22.7

Describe the Bug

I have a pretty nested query.

// fails
const result = await tx.query.projectinternalcost.findMany({
  with: {
    projectinternalcostitem: {
      with: {
          projecttransactionitems: {
              with: {
              projecttransaction: true //<- reverse many-to-one relation
              }
          },
          itemcategory: true
     },
  }
});

It results in this error:

[0]   code: '42703',
[0]   detail: 'There is a column named "projecttransaction_id" in table "project_projectinternalcosts_projectinternalcostitems_projecttr", but it cannot be referenced from this part of the query.',
[0]   hint: 'To reference that column, you must mark this subquery with LATERAL.',

I can make the query work if i go one level down:

// works
const result = await tx.query.projectinternalcostitem.findMany({
  with: {
    projecttransactionitems: {
      with: {
    projecttransaction: true
       }
    },
    itemcategory: true
    }
});

Or alternatively if i remove the reverse access:

// works
const result = await tx.query.projectinternalcost.findMany({
  with: {
    projectinternalcostitem: {
      with: {
          projecttransactionitems: true,
          itemcategory: true
     },
  }
});

The following query also fails (similar error message)

        const test = await tx.query.project.findFirst({
                where: (project, { eq }) => eq(project.id, projectId),
                with: {
                    projectquotations: {
                        with: {
                            quotationitemgroups: {
                                with: {
                                    quotationitems: {
                                        with: {
                                            item: {
                                                with: {
                                                    itemcategory: true // one-to-many query
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            });

If i remove the one-to-many access it works

        const test = await tx.query.project.findFirst({
                where: (project, { eq }) => eq(project.id, projectId),
                with: {
                    projectquotations: {
                        with: {
                            quotationitemgroups: {
                                with: {
                                    quotationitems: {
                                        with: {
                                            item: true // removed one-to-many access
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            });

But if i reduce the depth:

        const test = await tx.query.projectquotations.findFirst({
                        with: {
                            quotationitemgroups: {
                                with: {
                                    quotationitems: {
                                        with: {
                                            item: true
                                        }
                                    }
                                }
                            }
                        }
                    }
            });

This also works

Expected behavior

It should work in all conditions

Environment & setup

No response

[noobmaster19]

This is a table length issue in case someone is facing the same issue.

2066