This is the fourth day of my participation in the November Gwen Challenge. Check out the details: The last Gwen Challenge 2021

The problem

How to implement many-to-one query in TypeOrM, we use a question to analyze step by step

Table structure design

To make this clearer, we will use the table structure example of typeorm official document to modify it slightly:

+-------------+--------------+----------------------------+
|                         photo                           |
+-------------+--------------+----------------------------+
| id          | int(11)      | PRIMARY KEY AUTO_INCREMENT |
| url         | varchar(255) |                            |
| user_id      | int(11)      |               |
+-------------+--------------+----------------------------+

+-------------+--------------+----------------------------+
|                          user                           |
+-------------+--------------+----------------------------+
| id          | int(11)      | PRIMARY KEY AUTO_INCREMENT |
| name        | varchar(255) |                            |
+-------------+--------------+----------------------------+
Copy the code

There are two tables, photo and user, where one user can correspond to multiple photos (a person can take multiple photos). The photo table has the field userId, indicating who took the photo, corresponding to the ID in the user table.

Physical design

To directly map a table to an entity in typeOrM, write:

import {Entity, PrimaryGeneratedColumn, Column, ManyToOne} from "typeorm";
import {User} from "./User";

@Entity(a)export class Photo {
    
    @PrimaryGeneratedColumn({
        name: 'id'
    })
    id: number;
    
    @Column({
        name: 'url'
    })
    url: string;
    
    @Column({
        name: 'user_id'
    })
    userId: number;
    
}
Copy the code
import {Entity, PrimaryGeneratedColumn, Column, OneToMany} from "typeorm";
import {Photo} from "./Photo";

@Entity(a)export class User {
    
    @PrimaryGeneratedColumn({
        name: 'id'
    })
    id: number;
    
    @Column({
        name: 'name'
    })
    name: string;
}
Copy the code

The problem

The problem is that you want to find all the photos that match the criteria, and find their corresponding user information, and return the result like this:

[{
    id: 1,
    url: "https://photo_url",
    userId: 10000,
    user: {
        id: 10000}}, {id:2,
    url: "https://photo_url",
    userId: 10001,
    user: {
        id: 10001, name: 'li Si'}},]Copy the code

How to solve through SQL

SQL > select user.name from user.name;

SELECT `photo`.`id` AS `photo_id`, `user`.`name` AS `user_name` 
FROM `photo` `photo`
LEFT JOIN `user` `user`
ON `user`.`id` = photo.user_id
Copy the code

Implemented via TypeOrm

First of all, note that the actual result of our query is to stuff user’s data into photo. In TypeOrM, each query result must be mapped to the corresponding Model, so we need to add attributes to the entity structure we defined earlier

import {Entity, PrimaryGeneratedColumn, Column, ManyToOne} from "typeorm";
import {User} from "./User";
@Entity(a)export class Photo {
    
    @PrimaryGeneratedColumn({
        name: 'id'
    })
    id: number;
    
    @Column({
        name: 'url'
    })
    url: string;
    
    @Column({
        name: 'user_id'
    })
    userId: number;
    @ManyToOne(() = > User)
    @JoinColumn({ name: 'user_id' })
    public user: User;
}
Copy the code

As we can see, the new part is

@ManyToOne(() = > User)
@JoinColumn({ name: 'user_id' })
public user: User;
Copy the code

Let’s explain what they mean

ManyToOne

The official documentation is too simple, but you can also refer to it.

Orkhan. Gitbook. IO/typeorm/doc…

Official document translation: typeorm.bootcss.com/many-to-one…

This stackoverflow question explains some: stackoverflow.com/questions/5…

In simple terms, @manytoOne is used to define a many-to-one relational field. For example, in this case, multiple photos correspond to a user. Its decorator requires two functions. For example, @onetomany can be omitted if the @onetomany field is not defined.

The property he decorates is public user: user; His type is User

Modify the query mode

await getRepository(Photo)
    .createQueryBuilder('photo')
    .leftJoinAndSelect('photo.user'.'user') // Notice here
    .select('photo')
    .addSelect('user') / / and here
    .getMany();
Copy the code

Since the user attribute decorated by ManyToOne is of type user, the photo table can connect directly to photo.user, but the default rule for connecting, which is ON, is user.id=photo.userId, UserId is the default concatenation ID of the user attribute we just declared

But there is no userId foreign key column in our table, so we need to define our own column name, which is JoinColumn

JoinColumn

JoinColumn is used to define which table contains foreign keys, and you can also define the names of join columns and reference columns

When we set @JoinColumn, it automatically creates a column in the database called propertyName + referencedColumnName. Such as:

@ManyToOne(() = > User)
@JoinColumn(a)// this decorator is optional for @ManyToOne, but required for @OneToOne
public user: User;
Copy the code

This code will create a userId column in the database. If you want to change this name in the database, you can specify a custom join column name, as we did in our example

@ManyToOne(() = > User)
@JoinColumn({ name: 'user_id' })
public user: User;
Copy the code