Many to Many custom table

[enricrp8]

Hi, I am using typeorm and CRUD and I have a ManyToMany relation, however since that relation needs extra fields I need to manually create the entity. However, I don't know how it needs to be implemented or if CRUD gives support to it. Thanks

[Diluka]

Could you please supply an example about the entities and relations?

[jbjhjm]

It's not implemented yet. I can provide an example which I'm currently working on. Caution: It has issues when updating relations (when updating one of the main entity types. Seems like the relation entities can not be deleted via the default code so I need to build a way around and make sure to delete / re-add them manually.) Anyways here it is:

@Entity({name:'videos'})
export class VideoEntity {

    @PrimaryGeneratedColumn()
    id:number;
    @Column({unique:true})
    name:string;

    @OneToMany((type) => VideoCategoryMapEntity, (map) => map.video, { cascade:true, onDelete:'CASCADE' })
    videoCategoryMap: VideoCategoryMapEntity[];

}

@Entity({name:'videos-categories'})
export class VideoCategoryEntity extends VideoCategoryItem {

    @PrimaryGeneratedColumn()
    id:number;

    @Column()
    name:string;

    @OneToMany((type) => VideoCategoryMapEntity, (map) => map.category, { cascade:true, onDelete:'CASCADE', onUpdate:'CASCADE' })
    videoCategoryMap: VideoCategoryMapEntity[];

    items:VideoEntity[]

    @AfterLoad()
    mapVideos() {
        this.items = [];
        if(this.videoCategoryMap) this.videoCategoryMap.forEach(mapItem => {
            this.items.push(mapItem.video)
        })
    }

}

@Entity({name:'videos-categories-map'})
@Index(["videoId", "categoryId"], { unique: true })
export class VideoCategoryMapEntity {

    @PrimaryGeneratedColumn()
    id:number;

    @Column()
    @Index()
    videoId:number;

    @Column()
    @Index()
    categoryId:number;

    @Column({nullable:true})
    ordering:number;

    @ManyToOne(type => VideoCategoryEntity, 'videoCategoryMaps', {onDelete:'CASCADE'})
    public category: VideoCategoryEntity;

    @ManyToOne(type => VideoEntity, 'videoCategoryMaps', {onDelete:'CASCADE'})
    public video: VideoEntity;

}

And of course add the join query in controller decorator:

@Crud({
    //...
    query: {
        join:{
            videoCategoryMap: {
                eager:true,
                persist:['id']
            },
            'videoCategoryMap.video': {
                eager:true,
                // required is needed but not yet included in latest release.
                // without it it will only return categories which are assigned to 1+ items
                required:false, 
            }
        }
    }
})

So in a certain way this is a ManyToOneToMany relation. Pretty useful for manually ordered relations.

[BovineEnthusiast]

This would be very useful to have. I have Organization and User which are linked with an intermediate OrganizationToUser table. When I create an Organization I'd like to create an entry in the middle table at the same time using a nested object when calling createOne e.g.

// new organization payload
{
  name: 'someName',
  organizationToUsers: [/*...*/],
}

The reason for this is that the intermediate table describes permissions between the user and organization. In this case, I want to set the isOwner permission right away. I suppose in the mean time I can have a OneToMany relation for the ownership.

[michaelyali]

@enricrp8 @BovineEnthusiast if you have established ManyToMany relations in the right way (I mean in the way it's described in the TypeORM documentation) you'll be able to create your OrganizationToUser in the way you've mentioned. But if your relational table needs some extra columns, you need to create that entity class and connect it in your module TypeOrmModule.forFeature([Organization, OrganizationToUser]) I have an example here and here

[iturn]

I can confirm that @zMotivat0r solution works well for joining.

How would it work though in the controller with the out of the box services. Is this supported? Support is have 3 entities; members, bookings, participations. The participations table links members to bookings

I would then like to have a controller url like so members/:memberId/bookings

If i do a get on that url i get and error that tries the memberId param on the bookings table which fails; [ExceptionsHandler] ER_BAD_FIELD_ERROR: Unknown column 'BookingEntity.memberId' in 'where clause'

[silcki]

I can confirm that @zMotivat0r solution works well for joining.

How would it work though in the controller with the out of the box services. Is this supported? Support is have 3 entities; members, bookings, participations. The participations table links members to bookings

I would then like to have a controller url like so members/:memberId/bookings

If i do a get on that url i get and error that tries the memberId param on the bookings table which fails; [ExceptionsHandler] ER_BAD_FIELD_ERROR: Unknown column 'BookingEntity.memberId' in 'where clause'

maybe you should use

@Crud({
    model: {
        type: BookingEntity,
    },
    params: {
        memberId: {
            field: 'member', // real field of BookingEntity
            type: 'number'
        },
    },    
})

[iturn]

Tried that but it wont work as the real field on the bookingEntity is members.

[hakimio]

@zMotivat0r I am facing the same issue like @iturn : your solution works well for ManyToOne/OneToMany case but it doesn't work with MayToMany when there is an intermediate table in between connected the two entities.

[silcki]

I had the same issue and after not long debugging I can assume that it isn't a problem in the CRUD module, the problem in TypeORM seems TypeORM can't create SQL query when you try to do filtering by field ManyToMany for resolving this issue I had to create intermediate entity by the example of @zMotivat0r with an independent controller and service for it

[iturn]

Yes i've created a seperate service and controller as well as i could not get it to work with crud package in between. So i made some functions to keep accepting the exact same requests as the crud packge as to keep the api uniform

//controller

export class MemberBookingController {
  constructor(public service: BookingService) {}

  @Override()
  async getManyBase(@ParsedRequest() req: CrudRequest) {
    const flatRequest = flattenCrudRequest(req)

    return this.service.getManyByConditions(
      flatRequest.search,
      flatRequest.joins,
    )
  }
}

// service

@Injectable()
export class BookingService extends TypeOrmCrudService<BookingEntity> {
  constructor(
    @InjectRepository(BookingEntity, dmsDbConnection)
    private bookingRepository: Repository<BookingEntity>,
  ) {
    super(bookingRepository)
  }

  async getManyByConditions(
    searchConditions: SearchCondition[],
    joins: JoinRow[],
  ): Promise<BookingEntity[]> {
    let query = this.bookingRepository.createQueryBuilder('booking')

    query = appendJoins(query, 'booking', joins)

    query = appendSearchConditions(query, searchConditions)

    return await query.getMany()
  }
}

[hakimio]

@iturn Thanks for sharing your solution. Can you also share the custom functions you have created (flattenCrudRequest, appendJoins, appendSearchConditions, etc)?

[adrian88GitHub]

Excuse me, a query, how would sending a request work ?, I mean how do I send the data so that I can save a record. Because I test with postman and it only saves the record in a table and not in the intermediate table

[silcki]

@iturn I hope your solution works fine, I didn't check it, but I see one disadvantage in my opinion: what should I do if I need to apply pagination or filtering? my solution is to create a separate controller for the intermediate entity. URL looks like GET /categories/:id/videos - give me all videos for the specific category yes, as the response you will get the collection of VideoCategoryEntity, not VideoEntity thanks a lot, @zMotivat0r for his perfect example

// Entities

@Entity('video')
export class VideoEntity {

  @PrimaryGeneratedColumn()
  id:number;

  @Column()
  name:string;

  @ManyToMany((type) => CategoryEntity, (u) => u.videos, { cascade: true })
  @JoinTable({
    name: 'video_category',
    joinColumn: {
      name: 'videoId',
      referencedColumnName: 'id',
    },
    inverseJoinColumn: {
      name: 'categoryId',
      referencedColumnName: 'id',
      },
  })
  categories: CategoryEntity[];

  @OneToMany((type) => VideoCategoryEntity, (el) => el.video, {
    persistence: false,
    onDelete: 'CASCADE',
  })
  videoCategory: VideoCategoryEntity[];
}

@Entity('category')
export class CategoryEntity {

  @PrimaryGeneratedColumn()
  id:number;

  @Column()
  name:string;

  @ManyToMany(type => VideoEntity, videos => videos.categories)
  videos: VideoEntity[];

  @OneToMany((type) => VideoCategoryEntity, (el) => el.category, {
    persistence: false,
    onDelete: 'CASCADE',
  })
  videoCategory: VideoCategoryEntity[];
}

@Entity('video_category')
export class VideoCategoryEntity {

  @PrimaryColumn()
  public videoId: number;

  @PrimaryColumn()
  public categoryId: number;

  @ManyToOne((type) => VideoEntity, (el) => el.videoCategory, {
    primary: true,
    persistence: false,
    onDelete: 'CASCADE',
  })
  public video: VideoEntity;

  @ManyToOne((type) => CategoryEntity, (el) => el.videoCategory, {
    primary: true,
    persistence: false,
    onDelete: 'CASCADE',
  })
  public category: CategoryEntity;

}

// Controller
@Controller('categories/:id')
@Crud({
  model: {
    type: VideoCategoryEntity,
  },
  params: {
    id: {
      field: 'categoryId',
      type: 'number',
      primary: true,
    },
  },
  query: {
    join: {
      video: {
        eager: true,
      },
      category: {
        eager: true,
      },
    },
  },
  routes: {
    exclude: ['getManyBase', 'createOneBase', 'getOneBase', 'createManyBase', 'updateOneBase', 'replaceOneBase', 'deleteOneBase'],
  }
})
export class VideoCategoryController {
  constructor(
    public service: VideoCategoryService,
  ) {}

  @Get('videos')
  async getMany(
     @ParsedRequest() req: CrudRequest,
  ) {
    return this.service.getMany(req);
  }
}

[iturn]

@silcki In the case that you want the videoEntity my solution not needed. To put it in your terms. What my solution does is directly getting the videos of a category in a url as /category/1/videos through the intermediary table.

Then related to the filtering and pagination. Filtering and joins are implemented in the custom non crud package controller and service. Pagination could be implemented in the helpers funcs (flattenCrudRequest, appendJoins, appendSearchConditions) and service if needed.

[silcki]

Filtering and joins are implemented in the custom non crud package controller and service. Pagination could be implemented in the helpers funcs (flattenCrudRequest, appendJoins, appendSearchConditions) and service if needed.

It sounds a little bit strange if you are going to use some custom solutions for filtering and pagination why do you use CRUD module? please, don't take my words as an insult, but in my opinion, if you use some solutions of any module you should try to use maximum features of this module and create own custom solutions only when this module doesn't have opportunities to solve your problem

[iturn]

Yes very logical question. This is because this case only occurs in 2 controllers of the whole api. That is to say they are edge cases and the crud package is is huge benefit in the 99% of the cases. Then why create these customer functions and helpers? Well that is ofcourse to keep even these exceptional controllers from the api as if they are normal crud controller, whcih is very nice for the api consumer which can just filter, join etc like any other enpoint

[silcki]

understand, thank you for your detailed answer from our conversation I can do only one conclusion: this defect of TypeORM or CRUD module for working ManyToMany relation does a lot of pain

[iturn]

Well it causes some extra code in these specific controllers. If someone from the crud package core group could confirm that we are right and this is missing. We could try for to integrate this into the package by a pull request for these specific many to many through relationships?

[ahmadraza-gh]

I have same issue. I see this ticket was opened 1 year ago. is there any resolution on it? I have created another issue with my use-case that is almost the same as this Issue 592.