getting data in self-referencing relation with TypeORM

Question

This is my entity :

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

    @Column({ type: "text" })
    body: string

    @Column()
    displayName: string

    @OneToMany(() => Comment, comment => comment.parent, { eager: true })
    children: Comment[];

    @Column({ nullable: true })
    parentId: number

    @ManyToOne(() => Comment, comment => comment.children)
    @JoinColumn({ name: 'parentId' })
    parent: Comment

    @Column()
    status: CommentStatus
}

export enum CommentStatus {
    Visible = 1,
    InVisible = 2
}

fake data :

id     parentId     status     body
----------------------------------------
1      NULL         1          body-1
----------------------------------------
2      1            1          body-1-1
----------------------------------------
3      1            2          body-1-2
----------------------------------------
4      1            2          body-1-3

I want to retrieve the rows with the following conditions: parentId is NULL and status is CommentStatus.Visible

    const query = this.createQueryBuilder('comment')
        .leftJoinAndSelect('comment.children', 'parent')
        .where("comment.parentId IS NULL")
        .andWhere("comment.status = :status", { status: CommentStatus.Visible })

    const comments = await query.getMany()

It retrieves all the rows, because It does not check the status of children items why?

any help would be really appreciated

Answer 1

You need to add two different where clauses for the parent and their children.

For example-

const query = this.createQueryBuilder('comment')
    .leftJoinAndSelect('comment.children', 'parent')
    .where("comment.parentId IS NULL")
    .andWhere("comment.status = :status", { status: CommentStatus.Visible })
    .andWhere("parent.status = :status", { status: CommentStatus.Visible })

const comments = await query.getMany()