Typeorm many-to-many query

Question

Program entity:

export abstract class ProgramBase {
  @Column('varchar')
  programName: string;

  @Column('text')
  programDescription: string;

  @Column({ type: 'boolean', default: false })
  isDeleted: boolean;

  @Column({ type: 'integer', default: 1 })
  version: number;

  @Column({
    select: false,
    nullable: true,
    insert: false,
    update: false,
    type: 'varchar',
  })
  total: string;
}

@Entity()
@Unique(['programName'])
export class Program extends ProgramBase {
  @Index()
  @PrimaryGeneratedColumn()
  programId: number;

  tasks: Task[];
  @OneToOne(() => Tag, { onDelete: 'SET NULL' })
  @JoinColumn()
  tag: Tag;

  @ManyToOne(() => User, (user) => user.programs)
  @JoinColumn({ name: 'user' })
  user: User;

  @RelationId((program: Program) => program.user)
  userId: number;

  @OneToMany(() => ProgramTask, (pt) => pt.program)
  programtask: ProgramTask[];

  @OneToMany(() => Calendar, (c) => c.program)
  calendar: Calendar[];

  @OneToMany(() => ProgramSession, (ps) => ps.program)
  sessions: ProgramSession[];

  @OneToMany(() => ProgramCalendar, (pc) => pc.program)
  programCalendar: ProgramCalendar[];
}

ProgramTask entity:

export abstract class ProgramTaskBase {
  @Column('integer')
  order: number;

  @Column({ type: 'boolean', default: false })
  isReusable: boolean;
}

@Entity()
export class ProgramTask extends ProgramTaskBase {
  @PrimaryGeneratedColumn()
  programTaskId: number;

  @ManyToOne(() => Program)
  @JoinColumn({ name: 'program' })
  program: Program;

  @ManyToOne(() => Task, { onDelete: 'CASCADE' })
  @JoinColumn({ name: 'task' })
  task: Task;

  @RelationId((pt: ProgramTask) => pt.program)
  programId: number;

  @RelationId((pt: ProgramTask) => pt.task)
  taskId: number;
}

Task entity:

export abstract class TaskBase {
  @Column('integer')
  order: number;

  @Column('varchar')
  taskName: string;

  @Column('text')
  taskDescription: string;

  @Column('time')
  taskDuration: string;

  @Column('varchar')
  taskColor: string;

  @Column({ type: 'boolean', default: false })
  isReusable: boolean;
}

@Entity()
export class Task extends TaskBase {
  @PrimaryGeneratedColumn()
  taskId: number;

  @Column({ type: 'integer', select: false, nullable: true })
  version: number;

  @OneToMany(() => ProgramTask, (pt) => pt.task)
  programtask: ProgramTask[];

  @ManyToOne(() => User, (u) => u.tasks)
  @JoinColumn()
  user: User;

  @RelationId((task: Task) => task.user)
  userId: number;
}

I have Program, ProgramTask and Task tables in TypeOrm. Program and ProgramTask table have a one-to-many relation, also Task and ProgramTask tables have a one-to-many relation.

I need ProgramTask table to hold some additional columns. Now my wish is to get each program task without showing ProgramTask columns. For each program, I just want to see task of that program in result.

When I am using LeftJoinAndSelect method on query builder, I get the ProgramTask attributes, too. So I tried this:

const result = await programRepo
    .createQueryBuilder('program')
    .innerJoinAndSelect(
      'program.programtask',
      'program_task',
      'program.programId = program_task.program',
    )
    .innerJoinAndSelect(
      'program_task.task',
      'task',
      'task.taskId = program_Task.task',
    )
    .getMany();

In this query result only showing programs, not tasks in programs.

What should I do?

I am expecting this result:

{
    "program": {
        // some program attributes
        task: [{
                // some task attributes
               },{
               //some taks attributes
              }]
    }
}

UPDATE

I added tasks:Task[] attribute to the Program entity and tried this query:

 const result = await programRepo
        .createQueryBuilder('program')
        .innerJoin(
          'program.programtask',
          'program_task',
          'program.programId = program_task.program',
        )
        .innerJoinAndMapOne(
          'program.tasks',
          'program_task.task',
          'task',
          'task.taskId = program_task.task',
        )
        .getMany();

But it did not worked too.

Answer 1

In my case create entity with the relationship like this below

Program entity:

@Entity()
export class Program extends ProgramBase {
  @PrimaryGeneratedColumn()
   id: number;

  //...your column

  @OneToMany(() => ProgramTask, (pt) => pt.programtask)
  programtasks: ProgramTask[];
}

Task entity:

@Entity()
export class Task extends TaskBase {
  @PrimaryGeneratedColumn()
  id: number;

//your rest columns

  @OneToMany(() => ProgramTask, (pt) => pt.programtask)
  programtask: ProgramTask[];
}

ProgramTask entity:

@Entity()
export class ProgramTask extends ProgramTaskBase {
  @PrimaryGeneratedColumn()
  id: number;
  
  @ManyToOne(() => Program, (program) => program.programtasks)
  //@JoinColumn({ name: 'program' }) You can remove this, it will be auto generated based on your column name + Id (e.g.: programId)
  program: Program;

  @ManyToOne(() => Task, (task) => task.programtasks, { onDelete: 'CASCADE' })
  //@JoinColumn({ name: 'task' }) same on above
  task: Task;

// your rest columns
}

and the query will be like this:

const result = await programRepo
        .createQueryBuilder('program')
        .innerJoinAndSelect('program.programtask','programtask')
        .innerJoinAndSelect(
          'programtask.task',
          'task',
        )
        .getMany();