postgresql - TypeORM:[364] 错误:在字符 401 处缺少表“userorganisation”的 FROM 子句条目
问题描述
在使用和连接我为数据库TypeORM
设置的表时,我遇到了一个非常有趣的问题。Postgres
我想通了,但我想我会在这里为遇到类似问题的其他人发布信息。
我在我的数据库上设置了 3 个表:user
, organisation
, user_organisation
.
这样做的想法是,一个用户可以属于许多组织,并且名为user_organisation
将用户映射到这些组织的表。所以我的实体看起来像这样,
用户实体.ts
import { TimestampedEntity } from '@shared/entities/timestamped.entity';
import { Organisation } from '@user/entities/organisation.entity';
import { UserOrganisation } from '@user/entities/user-organisation.entity';
import { Column, Entity, Index, JoinTable, ManyToMany, OneToMany, PrimaryGeneratedColumn } from 'typeorm';
@Entity()
@Index(['email', 'password'])
export class User extends TimestampedEntity {
@PrimaryGeneratedColumn()
userId: number;
@Column({
length: 64
})
username: string;
@Column({
length: 500
})
email: string;
@Column({
length: 255
})
password: string;
@Column({
type: 'json',
})
details: any;
@Column({
nullable: true
})
refreshToken: string;
@OneToMany(type => UserOrganisation, userOrganisation => userOrganisation.user)
@JoinTable()
userOrganisations: UserOrganisation[];
}
用户组织.entity.ts
import { Organisation } from '@user/entities/organisation.entity';
import { User } from '@user/entities/user.entity';
import { Column, Entity, JoinColumn, ManyToOne, OneToOne, PrimaryColumn, PrimaryGeneratedColumn } from 'typeorm';
@Entity()
export class UserOrganisation {
@ManyToOne(type => User, user => user.userOrganisations, { primary: true })
user: User;
@ManyToOne(type => Organisation, organisation => organisation.userOrganisations, { primary: true })
organisation: Organisation;
}
组织实体.ts
import { TimestampedEntity } from '@shared/entities/timestamped.entity';
import { UserOrganisation } from '@user/entities/user-organisation.entity';
import { User } from '@user/entities/user.entity';
import { Column, Entity, JoinColumn, JoinTable, ManyToMany, ManyToOne, OneToMany, OneToOne, PrimaryGeneratedColumn } from 'typeorm';
@Entity()
export class Organisation extends TimestampedEntity {
@PrimaryGeneratedColumn()
orgId: number;
@Column({
length: 255
})
name: string;
@Column({
type: 'json'
})
details: any;
@OneToMany(type => UserOrganisation, userOrganisation => userOrganisation.organisation)
userOrganisations: UserOrganisation[];
}
然后我试图运行以下查询,
this.userRepository.createQueryBuilder('user')
.where('user.email = :email', { email })
.innerJoin(UserOrganisation, 'userOrganisation', 'user.userId = userOrganisation.userUserId')
.getOne();
这是我收到的错误信息,
ERROR: missing FROM-clause entry for table "userorganisation" at character 401
最后的查询是这样打印出来的,
SELECT "user"."createdAt" AS "user_createdAt"
, "user"."updatedAt" AS "user_updatedAt"
, "user"."userId" AS "user_userId"
, "user"."username" AS "user_username"
, "user"."email" AS "user_email"
, "user"."password" AS "user_password"
, "user"."details" AS "user_details"
, "user"."refreshToken" AS "user_refreshToken"
FROM "user" "user"
INNER JOIN "user_organisation" "userOrganisation"
ON "user"."userId" = userOrganisation.userUserId
WHERE "user"."email" = $1
我修复它的方式如下所述。
解决方案
我在查询中注意到的,
SELECT "user"."createdAt" AS "user_createdAt", "user"."updatedAt" AS "user_updatedAt", "user"."userId" AS "user_userId", "user"."username" AS "user_username", "user"."email" AS "user_email", "user"."password" AS "user_password", "user"."details" AS "user_details", "user"."refreshToken" AS "user_refreshToken" FROM "user" "user" INNER JOIN "user_organisation" "userOrganisation" ON "user"."userId" = userOrganisation.userUserId WHERE "user"."email" = $1
user
是不是表和userOrganisation
表的连接条件有区别,
"user"."userId" = userOrganisation.userUserId
该user
表自动用引号括起来,但userOrganisation
不是......所以我将代码更改为以下内容,
this.userRepository.createQueryBuilder('user')
.where('user.email = :email', { email })
.innerJoin(UserOrganisation, 'userOrganisation', '"user"."userId" = "userOrganisation"."userUserId"')
.getOne();
如果您看上面,我在连接条件中添加了引号。现在一切正常:)
希望这可以帮助!