Database Relations - bonds between legionaries

Data administrators! Consul Caesar.js noticed that our legion is growing and tributes are multiplying at ballista speed. It's time to learn how to connect different data, just as legionaries form alliances and cohorts!

What are relations in the legionaries' world?

Imagine that every legionary has their centurion, every cohort has a legion, and every tribute belongs to a specific legionary. These are all relations - connections between different objects in our Roman data treasury.

TypeORM allows us to create four types of relations:

• One-to-One (1:1) - like a legionary and their tribute map
• One-to-Many (1:N) - like a centurion and their legion
• Many-to-One (N:1) - like many legionaries in one cohort
• Many-to-Many (N:N) - like legionaries and their skills

One-to-One: A legionary and their personal tribute map

Every true legionary has their personal, secret map to the most precious tribute:

1// tribute-map.entity.ts
2import { Entity, PrimaryGeneratedColumn, Column, OneToOne, JoinColumn } from 'typeorm';
3import { Legionary } from './legionary.entity';
4
5@Entity('tribute_maps')
6export class TributeMap {
7 @PrimaryGeneratedColumn()
8 id: number;
9
10 @Column()
11 islandName: string;
12
13 @Column('decimal', { precision: 10, scale: 6 })
14 latitude: number;
15
16 @Column('decimal', { precision: 10, scale: 6 })
17 longitude: number;
18
19 @Column('text')
20 instructions: string;
21
22 @Column({ default: false })
23 isFound: boolean;
24
25 @OneToOne(() => Legionary, legionary => legionary.tributeMap)
26 @JoinColumn() // Column with foreign key
27 legionary: Legionary;
28}

1// legionary.entity.ts (update)
2import { OneToOne } from 'typeorm';
3import { TributeMap } from './tribute-map.entity';
4
5@Entity('legionaries')
6export class Legionary {
7 // ... previous fields
8
9 @OneToOne(() => TributeMap, tributeMap => tributeMap.legionary)
10 tributeMap: TributeMap;
11}

One-to-Many: A centurion and their legion

One centurion can have many legionaries under their command:

1// centurion.entity.ts
2import { Entity, PrimaryGeneratedColumn, Column, OneToMany } from 'typeorm';
3import { Legionary } from './legionary.entity';
4
5@Entity('centurions')
6export class Centurion {
7 @PrimaryGeneratedColumn()
8 id: number;
9
10 @Column({ length: 100 })
11 name: string;
12
13 @Column({ length: 100 })
14 legionName: string;
15
16 @Column('int')
17 yearsOfExperience: number;
18
19 @Column('decimal', { precision: 15, scale: 2 })
20 totalTributeValue: number;
21
22 @OneToMany(() => Legionary, legionary => legionary.centurion)
23 legion: Legionary[];
24}

1// legionary.entity.ts (update)
2import { ManyToOne, JoinColumn } from 'typeorm';
3import { Centurion } from './centurion.entity';
4
5@Entity('legionaries')
6export class Legionary {
7 // ... previous fields
8
9 @ManyToOne(() => Centurion, centurion => centurion.legion)
10 @JoinColumn({ name: 'centurion_id' })
11 centurion: Centurion;
12}

Many-to-Many: Legionaries and their skills

Legionaries can have many skills, and each skill can be mastered by many legionaries:

1// skill.entity.ts
2import { Entity, PrimaryGeneratedColumn, Column, ManyToMany } from 'typeorm';
3import { Legionary } from './legionary.entity';
4
5@Entity('skills')
6export class Skill {
7 @PrimaryGeneratedColumn()
8 id: number;
9
10 @Column({ length: 50, unique: true })
11 name: string;
12
13 @Column('text')
14 description: string;
15
16 @Column({ type: 'enum', enum: ['Basic', 'Intermediate', 'Advanced', 'Master'] })
17 difficulty: string;
18
19 @ManyToMany(() => Legionary, legionary => legionary.skills)
20 legionaries: Legionary[];
21}

1// legionary.entity.ts (update)
2import { ManyToMany, JoinTable } from 'typeorm';
3import { Skill } from './skill.entity';
4
5@Entity('legionaries')
6export class Legionary {
7 // ... previous fields
8
9 @ManyToMany(() => Skill, skill => skill.legionaries)
10 @JoinTable({
11 name: 'legionaries_skills', // Junction table name
12 joinColumn: { name: 'legionaries_id', referencedColumnName: 'id' },
13 inverseJoinColumn: { name: 'skill_id', referencedColumnName: 'id' }
14 })
15 skills: Skill[];
16}

Loading relations - searching for connected tributes

Eager Loading - automatic loading

1// Automatic loading with every query
2@OneToMany(() => Legionary, legionary => legionary.centurion, { eager: true })
3legion: Legionary[];

Lazy Loading - loading on demand

1// Loading only when needed
2@OneToMany(() => Legionary, legionary => legionary.centurion)
3legion: Promise<Legionary[]>;
4
5// Usage:
6const centurion = await centurionRepository.findOne({ where: { id: 1 } });
7const legion = await centurion.legion; // Only now loads the data

Explicit Loading - explicit loading with relations

1// legionary.service.ts
2@Injectable()
3export class LegionaryService {
4 constructor(
5 @InjectRepository(Legionary)
6 private legionariesRepository: Repository<Legionary>,
7 @InjectRepository(Centurion)
8 private centurionRepository: Repository<Centurion>,
9 ) {}
10
11 async findLegionaryWithAllData(id: number): Promise<Legionary> {
12 return await this.legionariesRepository.findOne({
13 where: { id },
14 relations: {
15 centurion: true,
16 tributeMap: true,
17 skills: true
18 }
19 });
20 }
21
22 async findCenturionWithLegion(id: number): Promise<Centurion> {
23 return await this.centurionRepository.findOne({
24 where: { id },
25 relations: {
26 legion: {
27 skills: true, // Also load each legionary's skills
28 tributeMap: true
29 }
30 }
31 });
32 }
33
34 async findLegionarysWithSkills(): Promise<Legionary[]> {
35 return await this.legionariesRepository.find({
36 relations: ['skills', 'centurion'],
37 order: {
38 name: 'ASC',
39 skills: {
40 name: 'ASC'
41 }
42 }
43 });
44 }
45}

Query Builder - advanced tribute searching

1@Injectable()
2export class AdvancedLegionaryService {
3 constructor(
4 @InjectRepository(Legionary)
5 private legionariesRepository: Repository<Legionary>,
6 ) {}
7
8 async findLegionarysWithMostTributes(): Promise<Legionary[]> {
9 return await this.legionariesRepository
10 .createQueryBuilder('legionaries')
11 .leftJoinAndSelect('legionary.centurion', 'centurion')
12 .leftJoinAndSelect('legionary.skills', 'skill')
13 .where('legionary.tributeCount > :minTributes', { minTributes: 10 })
14 .orderBy('legionary.tributeCount', 'DESC')
15 .limit(10)
16 .getMany();
17 }
18
19 async findCenturionsWithLargeLegion(): Promise<Centurion[]> {
20 return await this.centurionRepository
21 .createQueryBuilder('centurion')
22 .leftJoinAndSelect('centurion.legion', 'legionaries')
23 .having('COUNT(legionary.id) >= :minLegionSize', { minLegionSize: 5 })
24 .groupBy('centurion.id')
25 .orderBy('COUNT(legionary.id)', 'DESC')
26 .getMany();
27 }
28
29 async findSkillStatistics() {
30 return await this.legionariesRepository
31 .createQueryBuilder('legionaries')
32 .leftJoin('legionary.skills', 'skill')
33 .select('skill.name', 'skillName')
34 .addSelect('COUNT(legionary.id)', 'legionariesCount')
35 .addSelect('AVG(legionary.tributeCount)', 'avgTributes')
36 .groupBy('skill.name')
37 .orderBy('legionariesCount', 'DESC')
38 .getRawMany();
39 }
40}

Cascade operations - automatic actions

1@Entity('centurions')
2export class Centurion {
3 // ... other fields
4
5 @OneToMany(() => Legionary, legionary => legionary.centurion, {
6 cascade: ['insert', 'update'], // Automatically save/update legionaries
7 onDelete: 'SET NULL' // When centurion disappears, legionaries remain without a centurion
8 })
9 legion: Legionary[];
10}
11
12@Entity('legionaries')
13export class Legionary {
14 // ... other fields
15
16 @OneToOne(() => TributeMap, tributeMap => tributeMap.legionaries, {
17 cascade: true, // All operations
18 onDelete: 'CASCADE' // When legionary disappears, the map does too
19 })
20 tributeMap: TributeMap;
21
22 @ManyToMany(() => Skill, skill => skill.legionaries, {
23 cascade: ['insert'] // Only when adding new skills
24 })
25 @JoinTable()
26 skills: Skill[];
27}

Operations on relations

1@Injectable()
2export class RelationService {
3 constructor(
4 @InjectRepository(Legionary)
5 private legionariesRepository: Repository<Legionary>,
6 @InjectRepository(Centurion)
7 private centurionRepository: Repository<Centurion>,
8 @InjectRepository(Skill)
9 private skillRepository: Repository<Skill>,
10 ) {}
11
12 async assignLegionaryToCenturion(legionariesId: number, centurionId: number): Promise<Legionary> {
13 const legionaries = await this.legionariesRepository.findOne({ where: { id: legionariesId } });
14 const centurion = await this.centurionRepository.findOne({ where: { id: centurionId } });
15
16 legionary.centurion = centurion;
17 return await this.legionariesRepository.save(legionaries);
18 }
19
20 async addSkillToLegionary(legionariesId: number, skillId: number): Promise<Legionary> {
21 const legionaries = await this.legionariesRepository.findOne({
22 where: { id: legionariesId },
23 relations: ['skills']
24 });
25 const skill = await this.skillRepository.findOne({ where: { id: skillId } });
26
27 if (!legionary.skills.some(s => s.id === skill.id)) {
28 legionary.skills.push(skill);
29 return await this.legionariesRepository.save(legionaries);
30 }
31
32 return legionaries;
33 }
34
35 async removeSkillFromLegionary(legionariesId: number, skillId: number): Promise<Legionary> {
36 const legionaries = await this.legionariesRepository.findOne({
37 where: { id: legionariesId },
38 relations: ['skills']
39 });
40
41 legionary.skills = legionary.skills.filter(skill => skill.id !== skillId);
42 return await this.legionariesRepository.save(legionaries);
43 }
44
45 async promoteToCapitan(legionariesId: number, legionName: string): Promise<Centurion> {
46 const legionaries = await this.legionariesRepository.findOne({ where: { id: legionariesId } });
47
48 const newCenturion = this.centurionRepository.create({
49 name: legionary.name,
50 legionName: legionName,
51 yearsOfExperience: 0,
52 totalTributeValue: legionary.totalTributeValue
53 });
54
55 const centurion = await this.centurionRepository.save(newCenturion);
56
57 // Remove the legionary from the legion
58 await this.legionariesRepository.remove(legionaries);
59
60 return centurion;
61 }
62}

Advanced relations with additional data

Sometimes we need additional information in a many-to-many relation:

1// legionaries-skill.entity.ts - junction table with additional data
2import { Entity, PrimaryColumn, Column, ManyToOne, JoinColumn } from 'typeorm';
3import { Legionary } from './legionary.entity';
4import { Skill } from './skill.entity';
5
6@Entity('legionaries_skills')
7export class LegionarySkill {
8 @PrimaryColumn()
9 legionariesId: number;
10
11 @PrimaryColumn()
12 skillId: number;
13
14 @Column({ type: 'int', default: 1 })
15 level: number; // 1-10
16
17 @Column({ type: 'date' })
18 learnedAt: Date;
19
20 @Column({ type: 'int', default: 0 })
21 practiceHours: number;
22
23 @ManyToOne(() => Legionary, legionary => legionary.legionariesSkills)
24 @JoinColumn({ name: 'legionariesId' })
25 legionary: Legionary;
26
27 @ManyToOne(() => Skill, skill => skill.legionariesSkills)
28 @JoinColumn({ name: 'skillId' })
29 skill: Skill;
30}

1// Updated entities
2@Entity('legionaries')
3export class Legionary {
4 // ... other fields
5
6 @OneToMany(() => LegionarySkill, legionariesSkill => legionariesSkill.legionaries)
7 legionariesSkills: LegionarySkill[];
8}
9
10@Entity('skills')
11export class Skill {
12 // ... other fields
13
14 @OneToMany(() => LegionarySkill, legionariesSkill => legionariesSkill.skill)
15 legionariesSkills: LegionarySkill[];
16}

Relation performance

The N+1 problem

1// BAD example - N+1 queries
2async getBadCenturionsWithLegion(): Promise<any[]> {
3 const centurions = await this.centurionRepository.find(); // 1 query
4
5 const result = [];
6 for (const centurion of centurions) {
7 const legion = await this.legionariesRepository.find({
8 where: { centurion: { id: centurion.id } }
9 }); // N queries!
10 result.push({ centurion, legionCount: legion.length });
11 }
12
13 return result;
14}
15
16// GOOD example - one query
17async getGoodCenturionsWithLegion(): Promise<any[]> {
18 return await this.centurionRepository
19 .createQueryBuilder('centurion')
20 .leftJoinAndSelect('centurion.legion', 'legionaries')
21 .getMany(); // 1 query
22}

Indexing foreign keys

1@Entity('legionaries')
2@Index(['centurionId']) // Index on foreign key
3export class Legionary {
4 @ManyToOne(() => Centurion, centurion => centurion.legion)
5 @JoinColumn({ name: 'centurion_id' })
6 centurion: Centurion;
7}

Practical exercise

Try creating new relations:

1. Quest (mission) - One-to-Many with Legionary
2. Island - Many-to-Many with Legionary (visited islands)
3. Tribute - Many-to-One with Legionary, One-to-One with TributeMap

1// Your code here
2@Entity('quests')
3export class Quest {
4 // Implement relations
5}
6
7@Entity('islands')
8export class Island {
9 // Implement Many-to-Many relations
10}
11
12@Entity('tributes')
13export class Tribute {
14 // Connect with Legionary and TributeMap
15}

Summary

Congratulations! Now you're a true master of Roman relations! You can:

✓ Create all types of relations between entities ✓ Load connected data in various ways ✓ Use Query Builder for complex queries ✓ Manage cascade operations ✓ Optimize performance of relational queries

Now your legion can be fully organized, and tributes properly catalogued!