5

Database: postgres

ORM: Typeorm

Framework: express.js

I have a Table in which one of the fields, named projects is an array of strings. The type is set to "varchar" in the migration and the de decorator is set to "simple-array".

In my get route if I receive a query ?project=name_of_the_project it should try to find the project in the simple-array.

For the search my get route is like this:

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);
}); 

The problem is that I´m getting an error saying that the right side should be an array.

(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.

It has to be an array of string in this field and I can´t use an foreignKey.

Please find bellow my migration and model related to this issue:

Migration:

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");
    }
}

Model:

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;

EDIT - 01

In the docs I found out that the simple-array stores the strings separated by a comma. I thing this means it is a string with words separated by a comma. In this case is there a way to find the which row has the string in the projects field?

Link - https://gitee.com/mirrors/TypeORM/blob/master/docs/entities.md#column-types-for-postgres

Edit 02

The field projects stores the projects that the students are doing, so the database returns this json:

  {
    "id": "e586d1d8-ec03-4d29-a823-375068de23aa",
    "name": "First Lastname",
    "intra_id": "flastname",
    "projects": [
      "42cursus_libft",
      "42cursus_get-next-line",
      "42cursus_ft-printf"
    ]
  },
6
  • It looks like column projects should have datatype varchar[] (or text[]) rather than just varchar. Commented Jul 14, 2020 at 22:29
  • @GMB , I tryied this and I got the same error. Commented Jul 14, 2020 at 22:32
  • What is actually stored in projects? Can you provide a sample? Commented Jul 14, 2020 at 22:37
  • Can you query the database directly? What your ORM is producing is not helpful because it is formatted. There is a function you can try: .where(":project = ANY ( string_to_array(students.projects, ','))", { project: project }) You may need to adjust the delimiter to what the ORM is using. Commented Jul 14, 2020 at 22:47
  • @MikeOrganek , it worked! Please give an answer so I can check. Commented Jul 14, 2020 at 23:04

1 Answer 1

4

Based on the comments and updates to the question, @WincentyBertoniLech determined that the ORM was storing the projects array as a comma-delimited text value in the students.projects column.

We can use string_to_array() to turn this into the proper where criterion:

.where(":project = ANY ( string_to_array(students.projects, ','))", { project: project })
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.