数据库: postgres
ORM框架: Typeorm
Web框架: express.js
我有一个表格,其中一个字段名为projects
,它是一个字符串数组。在迁移中将其类型设置为"varchar"
,并且使用"simple-array"
装饰器。
在我的get路由中,如果我接收到查询?project=name_of_the_project
,就应该尝试在简单数组中查找项目。
为了进行搜索,我的get路由如下:
studentsRouter.get("/", async (request, response) => {
const { project } = request.query;
const studentRepository = getCustomRepository(StudentRepository);
const students = project
? await studentRepository
.createQueryBuilder("students")
.where(":project = ANY (students.projects)", { project: project })
.getMany()
: await studentRepository.find();
// const students = await studentRepository.find();
return response.json(students);
});
问题在于我遇到了一个错误,提示右侧应该是一个数组。
(node:38971) UnhandledPromiseRejectionWarning: QueryFailedError: op ANY/ALL (array) requires array on right side
at new QueryFailedError (/Users/Wblech/Desktop/42_vaga/src/error/QueryFailedError.ts:9:9)
at Query.callback (/Users/Wblech/Desktop/42_vaga/src/driver/postgres/PostgresQueryRunner.ts:178:30)
at Query.handleError (/Users/Wblech/Desktop/42_vaga/node_modules/pg/lib/query.js:146:19)
at Connection.connectedErrorMessageHandler (/Users/Wblech/Desktop/42_vaga/node_modules/pg/lib/client.js:233:17)
at Connection.emit (events.js:200:13)
at /Users/Wblech/Desktop/42_vaga/node_modules/pg/lib/connection.js:109:10
at Parser.parse (/Users/Wblech/Desktop/42_vaga/node_modules/pg-protocol/src/parser.ts:102:9)
at Socket.<anonymous> (/Users/Wblech/Desktop/42_vaga/node_modules/pg-protocol/src/index.ts:7:48)
at Socket.emit (events.js:200:13)
at addChunk (_stream_readable.js:294:12)
(node:38971) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1)
(node:38971) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code.
该字段必须是字符串数组,并且不能使用外键。
以下是与此问题相关的迁移和模型,请参考:
迁移:
import { MigrationInterface, QueryRunner, Table } from "typeorm";
export class CreateStudents1594744103410 implements MigrationInterface {
public async up(queryRunner: QueryRunner): Promise<void> {
await queryRunner.createTable(
new Table({
name: "students",
columns: [
{
name: "id",
type: "uuid",
isPrimary: true,
generationStrategy: "uuid",
default: "uuid_generate_v4()",
},
{
name: "name",
type: "varchar",
},
{
name: "intra_id",
type: "varchar",
isUnique: true,
},
{
name: "projects",
type: "varchar",
isNullable: true,
},
],
})
);
}
public async down(queryRunner: QueryRunner): Promise<void> {
await queryRunner.dropTable("students");
}
}
模型:
import { Entity, Column, PrimaryGeneratedColumn } from "typeorm";
@Entity("students")
class Student {
@PrimaryGeneratedColumn("uuid")
id: string;
@Column()
name: string;
@Column()
intra_id: string;
@Column("simple-array")
projects: string[];
}
export default Student;
编辑 - 01
在文档中,我发现simple-array
存储用逗号分隔的字符串。我认为这意味着它是由逗号分隔的单词组成的字符串。在这种情况下,有没有办法找到在“projects”字段中具有该字符串的行?
链接 - https://gitee.com/mirrors/TypeORM/blob/master/docs/entities.md#column-types-for-postgres
编辑02
字段“projects”存储学生正在进行的项目,因此数据库返回此JSON:
{
"id": "e586d1d8-ec03-4d29-a823-375068de23aa",
"name": "First Lastname",
"intra_id": "flastname",
"projects": [
"42cursus_libft",
"42cursus_get-next-line",
"42cursus_ft-printf"
]
},
projects
列应该使用数据类型varchar[]
(或text[]
),而不仅仅是varchar
。 - GMBprojects
实际上存储了什么?你能提供一个样例吗? - Mike Organek.where(":project = ANY ( string_to_array(students.projects, ','))", { project: project })
你可能需要根据ORM使用的分隔符进行调整。 - Mike Organek