ManyToMany relations

ManyToMany relation

ManyToMany relationships are used to link 2 different records from two tables using an intermediate table - JOIN

Let's assume that we have a list of students and the courses they are enrolled in. There can be many students and many courses, but neither students must be assigned to a specific course, nor must courses be assigned to a specific student.

@Table()
@NamedTable("students")
class Students extends Module {
    
    @AutoIncrementId()
    public id!: number;
    
    @Column({ type: ColumnType.VARCHAR(64) })
    public name!: string;
    
}

@Table()
@NamedTable("courses")
class Courses extends Module {

    @AutoIncrementId()
    public id!: number;

    @Column({ type: ColumnType.VARCHAR(64) })
    public title!: string;
    
}

But we need students to be able to enroll in courses, and courses to have students. That's what ManyToMany relationships are for.

@Table()
@NamedTable("students")
class Students extends Module {
    
    @AutoIncrementId()
    public id!: number;
    
    @Column({ type: ColumnType.VARCHAR(64) })
    public name!: string;
    
    @ManyToMany(() => Courses, (course) => course.students)
    @JoinTable()
    public courses!: Courses[];
    
}

@Table()
@NamedTable("courses")
class Courses extends Module {

    @AutoIncrementId()
    public id!: number;

    @Column({ type: ColumnType.VARCHAR(64) })
    public title!: string;
    
    @ManyToMany(() => Students, (student) => student.courses)
    public students!: Students[];
    
}

We have added one field to each class, but these fields are not physically added to the tables. Instead, a JOIN table is created that has 2 columns: students and courses (or others, depending on the name of the fields). The table itself has a name in the format table1_table2

ManyToMany decorators must refer to each other (to fields), and where you specify @JoinTable will only determine the order of columns in this table. Also in this decorator you can specify your own name for this table and set onDelete for foreign keys

Now when calling find methods in Repository, these connections will be automatically made friends.

const studentsRepository = new Repository(Students)
const students = await studentsRepository.find({}, { relations: [Courses] })

You can also specify the "eager" type for any relationship to avoid explicitly managing additional queries.

What if I need to get students from the course that the student received by this request is enrolled in?

This is where recursion and depth settings come into play. By default, the depth is 1. This means that there is no recursion, and if you access the students[0].courses[0].students field, the array will be empty anyway. If you specify depth at 0, then the relationships will not be loaded at all. And if the depth is greater than one, then ModularORM will recursively call the method responsible for handling ManyToMany relationships.

const students : Students | null = await studentsRepository.findOne({}, { relations: [Courses], depth: 1 })
students!.courses[0].students // Always will be empty
await studentsRepository.updateInstance(students, { relations: [Courses, Students], depth: 2 })
// Now these connections will be loaded too.
students!.courses[0].students

How to create relations?

Relationships can be created in two ways: by passing an array of relationships to the insert and update methods in the repository, or through a separate addRelation method in the repository.

When you pass an array of ManyToMany relationships to INSERT-like queries, they are automatically inserted into the JOIN table by the auto-increment key of the new records. When you do an UPDATE, if you leave this array empty, then the relationships will not be affected, but if you pass it, then the old relationships will be deleted and new ones will be added. DLETE queries first delete all relations from the JOIN table, and then from the main table. To delete or add one relation separately, you can use the corresponding methods in the repository. You can also delete all relations using the deleteAllRelations method

await coursesRepository.insert({ title: 'Programming' })
const course = new Courses()
course.id = 1;

await studentsRepository.insert({ name: 'Maxim', courses: [course] })

const results = await coursesRepository.find({}, { relations: [Students], depth: 2 });

results.length // to be 1

results[0].students.length // to be 1

await studentsRepository.insert({ name: 'Alice', courses: [course] });
await studentsRepository.insert({ name: 'Anastasiya' });

const aliceFound = await studentsRepository.findOne({ name: 'Alice' }, { relations: [Courses] })

aliceFound!.courses.length // to be 1

const courseResult = await coursesRepository.find({}, { relations: [Students] });

courseResult.length // to be 1
courseResult[0].students.length // to be 1

await coursesRepository.insert({ title: 'Cooking' })
await coursesRepository.insert({ title: 'Hobby horsing' })

const cooking = new Courses();
cooking.id = 1;

const hobbyHorsing = new Courses();
hobbyHorsing.id = 2;

await studentsRepository.insert({ name: 'Polina' })
await studentsRepository.insert({ name: 'Adolf' })
await studentsRepository.insert({ name: 'Magomed', courses: [hobbyHorsing] })

const cookingCourseResults = await coursesRepository.findOne(cooking, { relations: [Students] });
const hobbyCourseResults = await coursesRepository.findOne(hobbyHorsing, { relations: [Students] });

const polina = await studentsRepository.findOne({ name: 'Polina' }, { relations: [Courses] })
const magomed = await studentsRepository.findOne({ name: 'Magomed' }, { relations: [Courses] })

polina.courses.length // to be 0
magomed.courses.length // to be 1
cookingCourseResults.students.length // to be 1
hobbyCourseResults.students.length // to be 1

await studentsRepository.delete({ name: 'Magomed' })

if (hobbyCourseResults) {
    await coursesRepository.updateInstance(hobbyCourseResults, { relations: [Students] });

    hobbyCourseResults.students.length // to be 0
}

await coursesRepository.insert({ title: 'Painting' })

const course = new Courses();
course.id = 1;

await studentsRepository.insert({ name: 'Jorge', courses: [course] });

let results = await coursesRepository.findOne({}, { relations: [Students] });
if (!results) throw new Error('Courses is null');

results!.students.length // to be 1

await coursesRepository.update({ title: 'Painting #2' }, { title: 'Painting' });
await coursesRepository.updateInstance(results, { relations: [Students] });

results.students.length // to be 1

await coursesRepository.update({ title: 'Painting', students: [] }, { title: 'Painting #2' });
await coursesRepository.updateInstance(results);

results.students.length // to be 0

Next >