Typeorm: Question - Deep joining

[shotor]

Is the following possible with 1 query?

I have the following relationship:

User 1-* Account 1-* Expense *-1 Project

I'm trying to get all the projects a certain user has made expenses for through any of their accounts.

I can get as far as getting the expenses for each account:

    const user = await this.repo
      .createQueryBuilder('user')
      .innerJoinAndSelect('user.accounts', 'accounts')
      .leftJoinAndSelect('accounts.expenses', 'expenses')
      // next line breaks
      .leftJoinAndSelect('accounts.expenses.projects', 'projects')
      .where('user.id=:user_id')
      .setParameter('user_id', id)
      .getOne()

I've tried multiple variations of the breaking line - but I'm not sure if it's even possible what I'm trying to do.

[pleerock]

Try this way:

const user = await this.repo
      .createQueryBuilder('user')
      .innerJoinAndSelect('user.accounts', 'accounts')
      .leftJoinAndSelect('accounts.expenses', 'expenses')
      .leftJoinAndSelect('expenses.projects', 'projects')
      .where('user.id=:user_id')
      .setParameter('user_id', id)
      .getOne()

[shotor]

That gives me the following error:

Error: Relation with property path projects in entity was not found.

But it got me thinking, Expense has a ManyToOne to Project. So it's correct that it doesn't have a projects property, it simply has project (confirmed by looking at my code). Changing projects from plural to singular fixed the problem.

Question: Can you infinitely join relationships like this?

[pleerock]

Yeah but you are limited by number of joins your database supports (for example mysql can support max 64 joins, etc.)