Too long to see
In the previous use of MongoDB, if there is a relationship between the data of two collections, in order to inform the database of the relationship between these data, it needs to be carried out when creating the schema. Therefore, this Schmea is the prototype of the table.
postgresql is now replaced, but the data relationship will still exist. Here, it is still set in the prototype of the table. In cooperation with typeorm, the operation is actually carried out in entity.
1 to 1
When A row in table A has A relationship with A row in table B (which in turn holds), it is 1 to 1.
src/user/user.entity.ts
import { Column, Entity, JoinColumn, OneToOne, PrimaryGeneratedColumn } from 'typeorm';
import Address from '../address/address.entity';
@Entity()
class User {
@PrimaryGeneratedColumn()
public id: string;
@Column()
public name: string;
@Column()
public email: string;
@Column()
public password: string;
@OneToOne(() => Address)
@JoinColumn()
public address: Address;
}
export default User;
First, configure OneToOne in the entity that "owns" the relationship. The first parameter it accepts is a function. The parameter is empty and returns the name of the related table
#src/address/address.entity.ts
import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';
@Entity()
class Address {
@PrimaryGeneratedColumn()
public id: string;
@Column()
public street: string;
@Column()
public city: string;
@Column()
public country: string;
}
export default Address;
The so-called relational table is another entity. Take Address as an example. Note that there is no operation to establish a relationship in Address
Make 1-to-1 two-way effective
The function of JoinColumn decorator is to add a foreign key to the table that "owns" this 1-to-1 relationship, that is, to obtain the rows in another related table through the foreign key.
However, foreign keys are not enough. Now the 1-to-1 relationship is not bi-directional effective, that is, only the user table "knows" this relationship, and the address table does not know it. In order to make this relationship bi-directional, just add the second parameter in OneToOne.
#src/user/user.entity.ts
import { Column, Entity, JoinColumn, OneToOne, PrimaryGeneratedColumn } from 'typeorm';
import Address from '../address/address.entity';
@Entity()
class User {
@PrimaryGeneratedColumn()
public id: string;
@Column()
public name: string;
@Column()
public email: string;
@Column()
public password: string;
@OneToOne(() => Address, (address: Address) => address.user)
@JoinColumn()
public address: Address;
}
export default User;
The second parameter of OneToOne is also a function. Parameter x is the class name of the entity of the related table. At the same time, it returns a value in the form of x.property to tell typeorm what the fields in the related table are.
#src/address/address.entity.ts
import { Column, Entity, OneToOne, PrimaryGeneratedColumn } from 'typeorm';
import User from '../user/user.entity';
@Entity()
class Address {
@PrimaryGeneratedColumn()
public id: string;
@Column()
public street: string;
@Column()
public city: string;
@Column()
public country: string;
@OneToOne(() => User, (user: User) => user.address)
public user: User;
}
export default Address;
Now back to Address, the decorator of OneToOne is added to the user field. Obviously, parameters 1 and 2 are similar to those set in user. In this way, typeorm can tell pqsl the clear 1-to-1 relationship between the two fields of the two tables.
Note that JoinColumn is only used on one side of the two fields that establish a 1-to-1 relationship, which represents the 'owning side' of the relationship. When you check the database, only the owning side table stores the data id of the related table, that is, the foreign key.
By establishing a two-way relationship, the advantage is that you can easily obtain the data of the associated table. Even if the table you currently query does not have the relationship of 'own', because the relationship is two-way, even if the query does not use the table decorated with JoinColumn, the data on the other side can be easily obtained.
private getAllAddresses = async (request: express.Request, response: express.Response) => {
const addresses = await this.addressRepository.find({ relations: ['user'] });
response.send(addresses);
}
The data in the relational table can be obtained in reverse through the operation of passing in the relations hips above.
If this operation is really common enough, you can even configure the eager parameter to automatically fill the data of related tables when building the entity.
Similar to JoinColumn, only one side of two related tables can be configured with eager.
#src/user/user.entity.ts
import { Column, Entity, JoinColumn, OneToMany, OneToOne, PrimaryGeneratedColumn } from 'typeorm';
import Address from '../address/address.entity';
import Post from '../post/post.entity';
@Entity()
class User {
@PrimaryGeneratedColumn()
public id: string;
@Column()
public name: string;
@Column()
public email: string;
@Column()
public password: string;
@OneToOne(() => Address, (address: Address) => address.user, {
cascade: true,
eager: true,
})
@JoinColumn()
public address: Address;
}
export default User;
Automatically save related data
Now there is another problem. The relationship between address and user makes their data related, so they need to be saved separately, which is too complicated.
- Pass in the cascade option when building the entity
- The information is embedded in the corresponding address of the user
In this way, typeorm can automatically update the associated data of both tables
1 to many and many to 1
When A row in table A is related to many rows in table B, but A row in table B is related to only one row in table A, (such as author and post), it forms 1 to many (or many to 1)
# src/user/user.entity.ts
import { Column, Entity, JoinColumn, OneToMany, OneToOne, PrimaryGeneratedColumn } from 'typeorm';
import Address from '../address/address.entity';
import Post from '../post/post.entity';
@Entity()
class User {
@PrimaryGeneratedColumn()
public id: string;
@Column()
public name: string;
@Column()
public email: string;
@Column()
public password: string;
@OneToOne(() => Address, (address: Address) => address.user, {
cascade: true,
eager: true,
})
@JoinColumn()
public address: Address;
@OneToMany(() => Post, (post: Post) => post.author)
public posts: Post[];
}
export default User;
For posts added in user, OneToMany tells pqsl that this field has a one to many relationship with post, specifically to the author field of post
#src/post/post.entity.ts
import { Column, Entity, JoinColumn, ManyToOne, PrimaryGeneratedColumn } from 'typeorm';
import User from '../user/user.entity';
@Entity()
class Post {
@PrimaryGeneratedColumn()
public id?: number;
@Column()
public title: string;
@Column()
public content: string;
@ManyToOne(() => User, (author: User) => author.posts)
public author: User;
}
export default Post;
Conversely, using ManyToOne in post is to tell pqsl that there is a many to one relationship between the data in post and the data in user, which is specific to user Posts field.
Note that the table on one side of the manyToOne is used to store foreign keys.
Oneto many and manyToOne do not need to establish a two-way relationship, because they are inherently two-way and indispensable. That is, you can complete the query of associated data by passing in relationships or configuring eager
Many to many
Many to many means that A row in table A is related to many rows in table B, and vice versa.
For example, a post can have multiple categories, and there will be many posts in one category.
#src/post/post.entity.ts
import { Column, Entity, ManyToOne, PrimaryGeneratedColumn, ManyToMany, JoinTable } from 'typeorm';
import User from '../user/user.entity';
import Category from "../category/category.entity";
@Entity()
class Post {
@PrimaryGeneratedColumn()
public id?: number;
@Column()
public title: string;
@Column()
public content: string;
@ManyToOne(() => User, (author: User) => author.posts)
public author: User;
@ManyToMany(() => Category)
@JoinTable()
categories: Category[];
}
export default Post;
#src/category/category.entity.ts
import { Column, Entity, PrimaryGeneratedColumn } from 'typeorm';
@Entity()
class Category {
@PrimaryGeneratedColumn()
public id: string;
@Column()
public name: string;
}
export default Category;
Note that the JoinTable decorator is used here to tell pqsl to create a new table, that is to say, neither Post nor Category need to store the data of this relationship and store it in the new table.
In this way, you need to create several categories through the category controller, and then you can return to the post controller to send the data with category.
If you want to obtain the category data of the posts relationship, you can also pass in the relationships.
private getAllPosts = async (request: express.Request, response: express.Response) => {
const posts = await this.postRepository.find({ relations: ['categories'] });
response.send(posts);
}
private getPostById = async (request: express.Request, response: express.Response, next: express.NextFunction) => {
const id = request.params.id;
const post = await this.postRepository.findOne(id, { relations: ['categories'] });
if (post) {
response.send(post);
} else {
next(new PostNotFoundException(id));
}
}
Many to many relationships can also establish two-way relationships, but JoinTable exists only on one side
#src/post/post.entity.ts
import { Column, Entity, JoinTable, ManyToMany, ManyToOne, PrimaryGeneratedColumn } from 'typeorm';
import Category from '../category/category.entity';
import User from '../user/user.entity';
@Entity()
class Post {
@PrimaryGeneratedColumn()
public id?: number;
@Column()
public title: string;
@Column()
public content: string;
@ManyToOne(() => User, (author: User) => author.posts)
public author: User;
@ManyToMany(() => Category, (category: Category) => category.posts)
@JoinTable()
public categories: Category[];
}
export default Post;
#src/category/category.entity.ts
import Post from 'post/post.entity';
import { Column, Entity, ManyToMany, PrimaryGeneratedColumn } from 'typeorm';
@Entity()
class Category {
@PrimaryGeneratedColumn()
public id: string;
@Column()
public name: string;
@ManyToMany(() => Post, (post: Post) => post.categories)
public posts: Post[];
}
export default Category;
Once a two-way relationship is established, the data (reverse) query can be completed on the other side by passing in relationships