TypeORM中如何通过ManyToOne关联返回用户名而非用户ID？

我拥有User和Post两个实体，Post实体通过ManyToOne关联User实体。创建Post时传入用户ID可正常关联，但在查询Post的控制器中，返回结果未包含用户相关信息，现需修改查询逻辑，使返回结果中包含User实体的username字段。

现有代码 #

Post实体 #

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

@Entity('posts')
export class Post {
    @PrimaryGeneratedColumn('uuid')
    id: string;

    @ManyToOne(() => User)
    @JoinColumn()
    user: User

    @Column()
    title: string;

    @Column()
    desc: string;

    @CreateDateColumn({ 
        type: 'timestamp', 
        precision: 3
      })
      createdAt: Date;    
}

User实体 #

import { BeforeInsert, BeforeUpdate, Column, Entity, PrimaryGeneratedColumn } from "typeorm";
import bcrypt from 'bcryptjs';

@Entity('users')
export class User {
    @PrimaryGeneratedColumn('uuid')
    id: string;

    @Column()
    username: string;

    @Column()
    email: string;

    @Column()
    password: string;

    @Column({
        default: ''
    })
    tokenResetPass: string;

    @BeforeInsert()
    @BeforeUpdate()
    hashPassword() {
        this.password = bcrypt.hashSync(this.password, 8);
    }
}

查询Post的控制器 #

import { Request, Response } from "express";
import { AppDataSource } from "../../database/config";
import { Post } from "../../entities/Post";

class FindAllPostsController {
    async index(req: Request, res: Response) {
        try {
            const posts = await AppDataSource.manager.find(Post);
            return res.status(200).json(posts);
        } catch (err) {
            return res.status(500).json(err);
        }
    }
}

export default new FindAllPostsController();

当前返回示例 #

{
    "id": "27febc4a-1461-4146-a839-bb860a947de2",
    "title": "test 1",
    "desc": "testing a post",
    "createdAt": "2022-12-17T16:52:39.196Z"
}

期望返回示例 #

{
    "id": "27febc4a-1461-4146-a839-bb860a947de2",
    "user": "username",
    "title": "test 1",
    "desc": "testing a post",
    "createdAt": "2022-12-17T16:52:39.196Z"
}

解决方案 #

方式一：关联加载后映射结果 #

在find方法中添加relations选项加载关联的user实体，再将结果映射为期望的格式：

import { Request, Response } from "express";
import { AppDataSource } from "../../database/config";
import { Post } from "../../entities/Post";

class FindAllPostsController {
    async index(req: Request, res: Response) {
        try {
            // 关联加载user实体
            const posts = await AppDataSource.manager.find(Post, {
                relations: ['user']
            });
            
            // 转换为期望的返回格式
            const formattedPosts = posts.map(post => ({
                id: post.id,
                user: post.user.username,
                title: post.title,
                desc: post.desc,
                createdAt: post.createdAt
            }));

            return res.status(200).json(formattedPosts);
        } catch (err) {
            return res.status(500).json(err);
        }
    }
}

export default new FindAllPostsController();

方式二：使用QueryBuilder精准控制查询 #

通过TypeORM的QueryBuilder可以只查询需要的字段，减少数据传输：

import { Request, Response } from "express";
import { AppDataSource } from "../../database/config";
import { Post } from "../../entities/Post";

class FindAllPostsController {
    async index(req: Request, res: Response) {
        try {
            const posts = await AppDataSource
                .getRepository(Post)
                .createQueryBuilder('post')
                .leftJoinAndSelect('post.user', 'user')
                .select([
                    'post.id',
                    'post.title',
                    'post.desc',
                    'post.createdAt',
                    'user.username'
                ])
                .getMany();
            
            // 映射格式，将user.username转为顶层字段
            const formattedPosts = posts.map(post => ({
                ...post,
                user: post.user.username
            }));

            return res.status(200).json(formattedPosts);
        } catch (err) {
            return res.status(500).json(err);
        }
    }
}

export default new FindAllPostsController();

说明 #

• 方式一操作简单，适合需要完整User实体的场景，但会加载User的所有字段；
• 方式二更高效，仅查询所需字段，适合对性能有要求的场景；
• 两种方式都能实现期望的返回格式。

内容的提问来源于stack exchange，提问作者Routfin