[typeorm][mysql]两张表,没有建立外键,怎么关联查询?

[bronana]

两个 entity: user, user_profile

// base.ts
import { CreateDateColumn, Entity, PrimaryGeneratedColumn, UpdateDateColumn } from 'typeorm';

export abstract class BaseEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @CreateDateColumn({ type: 'timestamp', name: 'created_at' })
  created_at: Date;

  @UpdateDateColumn({ type: 'timestamp', name: 'updated_at' })
  updated_at: Date;
}

// user.ts
import { Entity, PrimaryGeneratedColumn, Column, CreateDateColumn, UpdateDateColumn } from 'typeorm';
import { BaseEntity } from './base';

@Entity('user')
export class User extends BaseEntity {
  @Column()
  first_name: string;

  @Column()
  last_name: string;

  @Column()
  age: number;
}

// user_profile.ts
import { Column, Entity } from 'typeorm';
import { BaseEntity } from './base';

@Entity('user_profile')
export class UserProfile extends BaseEntity {
  @Column()
  email: string;

  @Column()
  user_id: number;
}

数据库数据有 user

id	created_at	updated_at	first_name	last_name	age
1	2024-03-26 02:08:26.106077	2024-03-26 02:08:26.106077	Timber	Saw	25
2	2024-03-26 02:17:34.012850	2024-03-26 02:21:58.541499	aaa	a	11

user_profile

id	created_at	updated_at	email	user_id
1	2024-03-26 02:08:26.124340	2024-03-26 02:08:26.124340	[email protected]	1
2	2024-03-26 02:17:45.472608	2024-03-26 02:17:45.472608	[email protected]	2

index.ts 文件

import { AppDataSource } from './data-source';
import { User } from './entity/user';
import { UserProfile } from './entity/user_profile';

AppDataSource.initialize()
  .then(async () => {
    const userRepo = AppDataSource.getRepository(User);
    const data = await userRepo
      .createQueryBuilder('user')
      .leftJoinAndSelect(UserProfile, 'user_profile', 'user.id = user_profile.user_id')
      .where('user.id = :userId', { userId: 2 })
      .getOne();
    console.log(`☁️🚀☁️ `, data);
  })
  .catch((error) => console.log(error));

打印结果为:

☁️🚀☁️  User {id: 2, created_at: Tue Mar 26 2024 02:17:34 GMT+0800 (中国标准时间), updated_at: Tue Mar 26 2024 02:21:58 GMT+0800 (中国标准时间), first_name: 'aaa', last_name: 'a', …}
arg1: User {id: 2, created_at: Tue Mar 26 2024 02:17:34 GMT+0800 (中国标准时间), updated_at: Tue Mar 26 2024 02:21:58 GMT+0800 (中国标准时间), first_name: 'aaa', last_name: 'a', …}
age: 11
created_at: Tue Mar 26 2024 02:17:34 GMT+0800 (中国标准时间)
first_name: 'aaa'
id: 2
last_name: 'a'
updated_at: Tue Mar 26 2024 02:21:58 GMT+0800 (中国标准时间)
[[Prototype]]: BaseEntity

发现,user_profile 中的数据没有在里面(如 email 等),请问是怎么回事? 查看 typeorm 的 query 如下

QUERY : SELECT
`user`.`id` AS `user_id`,
`user`.`created_at` AS `user_created_at`,
`user`.`updated_at` AS `user_updated_at`,
`user`.`first_name` AS `user_first_name`,
`user`.`last_name` AS `user_last_name`,
`user`.`age` AS `user_age`,
`user_profile`.`id` AS `user_profile_id`,
`user_profile`.`created_at` AS `user_profile_created_at`,
`user_profile`.`updated_at` AS `user_profile_updated_at`,
`user_profile`.`email` AS `user_profile_email`,
`user_profile`.`user_id` AS `user_profile_user_id`
FROM
	`user` `user`
	LEFT JOIN `user_profile` `user_profile` ON `user`.`id` = `user_profile`.`user_id`
WHERE
	`user`.`id` = ? -- PARAMETERS: [2]

[Trim21]

用 `@JoinColumn` `@OneToOne` 可以建立软外键。

[IvanLi127]

好像没做实体关系就不会有，得用 getRawOne 之类的，然后手动填充。

[ultimate42]

用 leftJoinAndMapOne

[sujin190]

你这个 data 的返回类型是 User ，这个 User class 里边又没有 user_profile 相关的属性，否则岂不是不符合类型声明，ts 是有类型的吧，别想着 js 那一套 object 可以随便加属性的逻辑了

[HongXinss]

const postQuery = this.postsRepository
.createQueryBuilder('post')
.leftJoinAndMapMany('post.children', Bug, 'bug', 'bug.cardId=post.id');

没有外键但是有对应关系可以用这种方式

[bronana]

@HongXinss #5 这个方法可行