Query nested relations with leftJoinAndSelect() won't using proper parameter.

[GitHubCloud]

Issue Description

leftJoinAndSelect() won't use proper parameter.

Expected Behavior

This is a common comment model, that i want to get result like this:

[
  {
    ...
    "author": {
      // comment author info
    },
    "replys": [
      {
        ...
        "author": {
          // reply author info
        }
      }
    ]
  }
  ...
]

Steps to Reproduce

My Comment entity:

@Entity()
export class Comment {
  @PrimaryGeneratedColumn()
  id: number;

  @Column()
  content: string;

  @JoinColumn()
  @ManyToOne(() => User, (user) => user.comments, { eager: true })
  author: number;

  @JoinColumn()
  @ManyToOne(() => Article, (article) => article.comments)
  article: number;

  @JoinColumn()
  @ManyToOne(() => Comment, (comment) => comment.replys)
  replyTo: number;

  @JoinColumn()
  @OneToMany(() => Comment, (comment) => comment.replyTo)
  replys: number;

  @CreateDateColumn()
  @Transform((d) => moment(d.value).toDate().getTime())
  createTime: Date;
}

I use query builder like this:

const queryBuilder = getRepository(Comment)
      .createQueryBuilder('comment')
      .leftJoinAndSelect('comment.author', 'author')
      .leftJoinAndSelect('comment.replys', 'replys')
      .leftJoinAndSelect('replys.author', 'replys.author')
      .where(query);

Then i get sql:

SELECT * FROM `comment` `comment`
         LEFT JOIN `comment` `replys` ON `replys`.`replyToId` = `comment`.`id`
         LEFT JOIN `user` `replys.author` ON `replys`.`authorId` = `replys`.`authorId`
         LEFT JOIN `user` `author` ON `author`.`id` = `comment`.`authorId`
WHERE (`comment`.`articleId` = ? AND `comment`.`replyToId` IS NULL)

Notice that on line 3:

`replys`.`authorId` = `replys`.`authorId`

This will get all the replys from Comment, and lead to all the replys.author becomes the first user who given reply.

Work Around

I have figured it out by change query builder to:

const queryBuilder = getRepository(Comment)
      .createQueryBuilder('comment')
      .leftJoinAndSelect('comment.author', 'author')
      .leftJoinAndSelect('comment.replys', 'replys')
      .leftJoinAndSelect('replys.author', 'replys.author', '`replys.author`.id = replys.author')
      .where(query);

And the generated sql will be:

SELECT * FROM `comment` `comment`
         LEFT JOIN `comment` `replys` ON `replys`.`replyToId` = `comment`.`id`
         LEFT JOIN `user` `replys.author` ON `replys`.`authorId` = `replys`.`authorId` AND (`replys.author`.id = `replys`.`authorId`)
         LEFT JOIN `user` `author` ON `author`.`id` = `comment`.`authorId`
WHERE (`comment`.`articleId` = ? AND `comment`.`replyToId` IS NULL)

Still, I want to know is there a better way to do it?

My Environment

Dependency	Version
Operating System	CentOS
Node.js version	v14.16.0
Typescript version	v4.2.3
TypeORM version	v0.2.31

Additional Context

Relevant Database Driver(s)

• aurora-data-api
• aurora-data-api-pg
• better-sqlite3
• cockroachdb
• cordova
• expo
• mongodb
• mysql
• nativescript
• oracle
• postgres
• react-native
• sap
• sqlite
• sqlite-abstract
• sqljs
• sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time, and I know how to start.
• Yes, I have the time, but I don't know how to start. I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

[dmitrii334]

I'm using posgres and your work around didn't fix it for me.
I've been able to make it work by doing .leftJoinAndMapMany for one-to-many relation entity and then doing .leftJoinAndMapOne for nested relation (it was many-to-one in my case).
Make sure to provide condition parameter to both mappers, it doesn't work otherwise.
I'm not sure this workaround provides at least half decent SQL to be honest, so use it at your own risk.

+1 for making such a join to work with simple .leftJoinAndSelects.