How to select records having array containing the passed in string from postgres/typeorm

Question

Following is the record in table -

I need to select records based on the batches (last column). I have my end point like -

http://localhost:3000/batch/Batch 3

This is my controller and works fine and redirect the call to service -

@Get('/batch/:batchName')
  getByBatch(@Param('batchName') batch): Promise<Assignment[]> {
    return this.assignmentService.getByBatch(batch);
  }

This is my service -

async getByBatch(batch: string): Promise<Batch[]> {
    return await getRepository(Assignment)
      .createQueryBuilder('a')
      .where('a.batches CONTAINS :batch', { batch: batch })
      .getMany();
  }

I am not sure as how the query would be formed in this case. I am using typeorm and postgres. For simple queries, I have directly used repository like -

return this.assgRepository.findOne({ id: id });

Following is the entity -

@PrimaryGeneratedColumn()
  id: number;

  @Column()
  title: string;

  @Column()
  description: string;

  @Column({ array: true })
  batches: string;

  @Column({ type: 'timestamp', default: () => 'CURRENT_TIMESTAMP' })
  createdAt: string;

I need to pass a batch and fetch records which ever contain that batch.

P.S. If you could also point me as how can I update the batches would be great.

orimdominic · Accepted Answer · 2021-08-17 09:11:34Z

1

@Column({type: "simple-array"})
batches: string;

batches should be saved in the form ["batch 1", "batch 2"] and not like [{"batch 1", "batch 2"}]

finally query

where("a.batches IN (:...batch)", { batch: ["batch 1"] })

edited Aug 17, 2021 at 9:11

orimdominic

1,13514 silver badges26 bronze badges

answered Jun 7, 2020 at 6:53

Kallol Medhi

6091 gold badge8 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

3ximus · Accepted Answer · 2022-08-22 17:30:20Z

0

This is how I'm doing it and it's

I have my entity like so:

  @Column({ type: 'text', array: true, nullable: true })
  relations?: string[];

I can save using the repository:

await repository.save([{
  relations: ['contact', 'user'],
}]);

And query using the query builder, returning all records that have 'contact' in the "relations" array:

  await repository
    .createQueryBuilder()
    .where('relations && ARRAY[:...relationTable]', {
      relationTable: ['contact'],
    });

answered Aug 22, 2022 at 17:30

3ximus

4095 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to select records having array containing the passed in string from postgres/typeorm

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related