Repository navigation
ManyToOne and OneToMany relations
Relations are syntactic sugar that allows you to automatically load related elements from the database of other tables when calling a method in the repository.
ManyToOne and OneToMany are used to describe relationships where one row in one table is linked to many rows from another table. And with decorators you can automatically load them
Let's look at the most common example with posts and comments. Let's imagine that we are writing a microservice for a social network where people can create posts and write comments under them. We have two tables:
@Table()
@NamedTable("posts")
class Posts extends Module {
@AutoIncrementId()
public id!: number;
@Column({ type: ColumnType.VARCHAR(64), notNull: true, index: true })
public userId!: string;
@Column({ type: ColumnType.VARCHAR(512), notNull: true })
public title!: string;
@Column({ type: ColumnType.TEXT, notNull: true })
public content!: string;
}
@Table()
@NamedTable("comments")
class Comments extends Module {
@AutoIncrementId()
public id!: number;
@Column({ type: ColumnType.VARCHAR(64), notNull: true, index: true })
public userId!: number;
@Column({ type: ColumnType.TEXT, notNull: true })
public content!: string;
@Column({
type: ColumnType.INTEGER,
index: true,
notNull: true,
foreignKey: {
referencedTable: "posts",
referencedColumn: "id"
},
onDeleteForeign: 'CASCADE'
})
public postId!: number;
}But what if we need to get all the comments under a post? Do we need to write a second query and get all the comments through it? No, that's what relationships are for. Let's look at creating similar tables, but using relationships
@Table()
@NamedTable("posts")
class Posts extends Module {
@AutoIncrementId()
public id!: number;
@Column({ type: ColumnType.VARCHAR(64), notNull: true, index: true })
public userId!: string;
@Column({ type: ColumnType.VARCHAR(512), notNull: true })
public title!: string;
@Column({ type: ColumnType.TEXT, notNull: true })
public content!: string;
@OneToMany(() => Comments, (comment) => comment.postId)
public comments!: Comments[];
}
@Table()
@NamedTable("comments")
class Comments extends Module {
@AutoIncrementId()
public id!: number;
@Column({ type: ColumnType.VARCHAR(64), notNull: true, index: true })
public userId!: number;
@Column({ type: ColumnType.TEXT, notNull: true })
public content!: string;
@ManyToOne(() => Posts, (post) => post.comments)
public postId!: number;
}The above code will be completely equivalent to the previous version, but now we can use all the repository "magic":
const postsRepository = new Repository(Posts);
const posts : Posts[] = await postsRepository.find({}, { relations: [Comments] });
posts.comments // List of post commentsNow ModularORM will execute the second SELECT query for us to load all comments by the auto-incrementing key.
Note that in the find methods, you need to specify the necessary links so that these arrays are loaded. If you do not do this, the array will be empty. But is it possible to automatically load all the links? Yes, it is! To do this, go back to our Posts table
@Table()
@NamedTable("posts")
class Posts extends Module {
@AutoIncrementId()
public id!: number;
@Column({ type: ColumnType.VARCHAR(64), notNull: true, index: true })
public userId!: string;
@Column({ type: ColumnType.VARCHAR(512), notNull: true })
public title!: string;
@Column({ type: ColumnType.TEXT, notNull: true })
public content!: string;
@OneToMany(() => Comments, (comment) => comment.postId, { loadType: 'eager' })
public comments!: Comments[];
}We specified the loading type for the relations. Before we did that, the default type was "mixed", which required us to load relations manually. With the "eager" type, all relations are automatically loaded. However, that doesn’t mean "mixed" is useless—on the contrary: if you don’t need the relations in certain places, we recommend keeping the "mixed" type and managing the loading manually. Otherwise, eager loading of relations may consume time and machine resources.
Note: ManyToOne is replaced by Column with a Foreign key to the specified table and an auto-increment key. This column goes to the physical database. And OneToMany is syntactic sugar that only specifies the field to which the array of relationships should be given. It is not inserted into the physical table.
Also, the relationships will not work if the tables do not have auto-increment keys specified.
Of course, these two relationships can work with DTO. The main difference will be that the OneToMany relationship will need to be specified in the DTO itself.
@Table()
@NamedTable("posts")
class Posts extends Module {
@AutoIncrementId()
public id!: number;
@Column({ type: ColumnType.VARCHAR(64), notNull: true, index: true })
public userId!: string;
@Column({ type: ColumnType.VARCHAR(512), notNull: true })
public title!: string;
@Column({ type: ColumnType.TEXT, notNull: true })
public content!: string;
@OneToMany(() => Comments, (comment) => comment.postId)
public comments!: Comments[];
}
class PostDTO {
@Result()
public id!: number;
@Result()
public userId!: string;
@Result()
content!: string;
@OneToMany(() => Comments, (comment) => comment.postId, { result: () => CommentDTO })
public comments!: CommentDTO[];
}
@Table()
@NamedTable("comments")
class Comments extends Module {
@AutoIncrementId()
public id!: number;
@Column({ type: ColumnType.VARCHAR(64), notNull: true, index: true })
public userId!: number;
@Column({ type: ColumnType.TEXT, notNull: true })
public content!: string;
@ManyToOne(() => Posts, (post) => post.comments)
public postId!: number;
}
class CommentDTO {
@Result()
public id!: number;
@Result()
public userId!: string;
@Result()
public content!: string;
@Result()
public postId!: number;
}Here we moved the OneToMany relationship to the DTO class, because OneToMany is not a physical column, but only syntactic sugar. But we did not move the ManyToOne relationship to the DTO, because it is a physical column.