How to join 3 relation table using typeorm nestjs

Question

I am using typeorm with NestJs, I try to join 3 relations but got an error path comment.user in entity was not found

Here is my table User

Id	username
1	row

 @PrimaryGeneratedColumn()
  id: number;
  @Column()
  username: string;
  @OneToMany(() => PostEntity, (post) => post.user, { eager: true })
  posts: PostEntity[];

Here is my table Posts

Id	desc	user_id
1	text	1

 @PrimaryGeneratedColumn()
  id: number;
  @Column()
  desc: string;
  @Column()
  userId: number;

  @ManyToOne(() => User, (user) => user.posts, {
    eager: false,
    onDelete: 'CASCADE',
  })
  user: User[];
  @OneToMany(() => CommentEntity, (comment: CommentEntity) => comment.post, {
    eager: true,
    onDelete: 'CASCADE',
  })
  comment: CommentEntity[];

Here is my table comment

Id	comment	user_id	postId
1	comment post 1	1	1

 @PrimaryGeneratedColumn()
  id: number;
  @Column()
  comment: string;
  @Column()
  userId: number;
  @Column()
  postId: number;
  @ManyToOne(() => PostEntity, () => (post: PostEntity) => post.comment, {
    eager: false,
    onDelete: 'CASCADE',
  })

  post: PostEntity[];
  @OneToOne(() => User)
  @JoinColumn()
  user: User;

In here I use OneToOne because 1 comment can comment by only 1 user

this is what my data look like

```
this.createQueryBuilder('post')
      .leftJoinAndSelect('post.user', 'user')
      .leftJoinAndSelect('post.comment', 'comment')
      .select(['post', 'user.id', 'user.username', 'comment'])
      .getMany();
```

this is what I get

[
  {
    id: 1,
    description: 'comment post 1',
    userId: 1,
    user: {
      id: 1,
      username: 'row',
    },
    comment: [
      {
        id: 1,
        description: 'comment',
        userId: 1,
        postId: 1,
      },
    ],
  },
];

In comment I want to join userId for user that comment . I want data look like this

[
  {
    id: 1,
    description: 'comment post 1',
    userId: 1,
    user: {
      id: 1,
      username: 'row',
    },
    comment: [
      {
        id: 1,
        description: 'comment',
        userId: 1,
        postId: 1,
        user: {
          // if different user comment will show different username
          id: 1, 
          username: 'row',
        },
      },
    ],
  },
];

This is what I try to do

this.createQueryBuilder('post')
      .leftJoinAndSelect('post.user', 'user')
      .leftJoinAndSelect('post.comment', 'comment')
      .leftJoinAndSelect('post.comment.user', 'user') 
      .select(['post', 'user.id', 'user.username', 'comment'])
      .orderBy('post.updated_at', 'DESC')
      .getMany();


 .leftJoinAndSelect('post.comment.user', 'user') // In this line I want to join userId but Its show an error  path comment.user in entity was not found

UPDATE

I try to use this ( this mean PostEntity)

this.find({ relations: ['user', 'comment', 'comment.user']

but still not working

Answer 1

Update your Comment entity's user relationship like this:

@OneToOne(() => User)
@JoinColumn({name: 'userId'})
user: User;

Then try:

this.createQueryBuilder('post')
      .leftJoinAndSelect('post.user', 'user')
      .leftJoinAndSelect('post.comment', 'comment')
      .leftJoinAndSelect('comment.user', 'commentedUser') 
      .select(['post', 'user.id', 'user.username', 'comment', 'commentedUser'])
      .orderBy('post.updated_at', 'DESC')
      .getMany();