CODEWITHSUNDEEP
node.jspostgresqltypescripttypeorm
Ohad Sahar
Ohad Sahar

Reputation: 121

How to delete many rows typeorm - postgresql & node.js(typescript)

Hi friends this is my function, it gets an array of ids I want to erase the rows in one stroke and not run in the loop, and can't find a solution to that. Would appreciate help.

async remove(ids: DeleteEmployeeAnswerDTO): Promise<boolean> {
        if (ids.employeeAnswersIds.length) {
            for (const id of ids.employeeAnswersIds) {
                await EmployeeAnswers.delete(id.id);
            }
        }
        return true;
    }

Upvotes: 11

Views: 43113

Answers (5)

Normal
Normal

Reputation: 3606

I was able to achieve that using this:

const items = req.body.linesIds.map((id) => ({ id, companyId: req.$USER?.companyId }))
// @ts-expect-error
const dbRes = await Line.delete(items)

This sends a single query, and even though it gives a type error, but it works perfectly fine, I believe this is something to change in the TypeORM type detention

Upvotes: 0

aalauno
aalauno

Reputation: 11

Previously I had the same issue. My case could help if you use NestJS + typeorm.

Here is a module:

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';

import { TaskController } from './task.controller';
import { Task } from './task.entity';
import { TaskService } from './task.service';

@Module({
  imports: [TypeOrmModule.forFeature([Task])],
  controllers: [TaskController],
  providers: [TaskService],
  exports: [TaskService],
})
export class TaskModule {}

Here is a part of my controller:

import { Controller, Delete, Query, Request } from '@nestjs/common';
import { TaskService } from './task.service';


@Controller('task')
export class TaskController {
  constructor(private taskService: TaskService) {}

  @Delete('/multiple/')
  async deleteMultiple(@Request() req, @Query('ids') ids: string[]) {
    const userId = req.user.id;

    try {
      await this.taskService.deleteMultiple(userId, ids);

      return 'success';
    } catch (error) {
      return 'error';
    }
  }
}

Here is a service:

import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { In, Repository } from 'typeorm';

@Injectable()
export class TaskService {
  constructor(@InjectRepository(Task) private repo: Repository<Task>) {}

  async deleteMultiple(userId: string, taskIds: string[]) {
    return await this.repo.delete({ userId, id: In(taskIds) });
  }
}

So, the solution is In(taskIds)

I hope it could be useful for someone.

Upvotes: 1

Khanh Nguyen
Khanh Nguyen

Reputation: 19

Use the "clear" method to clear all data recorded in the table!

async deleteProducts() {
  await this.productRepository.clear();
  return {
    message: MESSAGE.PRODUCTS_REMOVED
  };
}

Upvotes: -4

jack.benson
jack.benson

Reputation: 2363

If your table has a single ID column then you should be able to pass an array of IDs:

await EmployeeAnswers.delete(ids.employeeAnswersIds);

You could also specify multiple IDs in your where clause using In:

await EmployeeAnswers.delete({ id: In(ids.employeeAnswersIds) });

However if you deal with a table that has a composite primary key, like in my case, the following example can be the solution for you. I'm not crazy about this answer, but here is how I overcame this problem using DeleteQueryBuilder (docs):

async remove(ids: DeleteEmployeeAnswerDTO): Promise<boolean> {
  if (ids.employeeAnswersIds.length) {
    const deleteQueryBuilder = EmployeeAnswer.createQueryBuilder().delete()

    const idClauses = ids.map((_, index) => `ID = :id${index}`)
    const idClauseVariables = ids.reduce((value: any, id, index) => {
      value[`id${index}`] = id
      return value
    }, {})

    await deleteQueryBuilder.where(idClauses.join(' OR '), idClauseVariables).execute()
  }
  return true;
}

Upvotes: 12

cksrc
cksrc

Reputation: 2347

You can search for multiple records and then delete the entities found in a single operation. If one or more entities are not found, then nothing is deleted.

async removeMany(ids: string[]) {
    const entities = await this.entityRepository.findByIds(ids);
    if (!entities) {
      throw new NotFoundException(`Some Entities not found, no changes applied!`);
    }
    return this.entityRepository.remove(entities);
  }

Upvotes: 1

Related Questions