Many-to-many with custom column

Question

I'm fairly new to Postgres (and Typeorm), and having a really hard time figuring out how to handle the following scenario:

• I have a table called blog and a table called category . Blogs can belong to many categories, and categories can have many blogs. So I believe I need a "many to many" relationship here.
• My join table needs to have a column called is_primary, so since I need an additional custom column, I believe I need to create my own entity for this, instead of using the auto-generated one. I'm calling this table blogCategory. I'm getting tripped up here on how to handle the save when I need to set the is_primary flag to true.

Here is my schema:

// blog.entity.ts  omitting some fields for brevity
@Column()
@PrimaryGeneratedColumn()
id: number; 

@Column()
title: string; 

@OneToMany(() => BlogCategory, blogCategory => blogCategory.blog)
blogCategories: BlogCategory[];


// category.entity.ts
@Column()
@PrimaryGeneratedColumn()
id: number; 

@Column()
name: string;

@OneToMany(() => BlogCategory, blogCategory => blogCategory.category)
blogCategories: BlogCategory[];

// blog-category.entity.ts
@PrimaryGeneratedColumn()
id: number;

@Column()
is_primary: boolean;

@Column()
categoryId: number;

@Column()
blogId: number;

@ManyToOne( () => Category, category => category.blogCategories )
category: Category

@ManyToOne( () => Blog, blog => blog.blogCategories )
blog: Blog

I think that is all correct, but please correct me if I'm wrong. But the part I'm getting tripped up on now is how I actually handle the blog insert. I'm not sure how I handle adding the is_primary flag when necessary. Do I actually insert the blogCategory item manually? In which case I'd need to save the blog first, so that I'd have access to its id for the blogCategory. I was kind of under the impression that item would be created automatically.

Thanks in advance for anyone who is able to offer some insight!

Answer 1

You can achieve automatic insertion using {cascade: true} in your entity relation. Check this post How to save relation in @ManyToMany in typeORM

Other way you can do it using transaction. First save Blog, get the blog id then add item into BlogCategory See https://typeorm.io/#/transactions For an example see this pseudo code,

await getManager().transaction(async transactionalEntityManager => {
                const blog = await transactionalEntityManager.save(blogObject);
                blogCategory.blogId = blog.id;
                blogCategory.CategoryId = categoryId; // get categoryId from request body
                await transactionalEntityManager.save(blogCategory);
            });