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

user_13176130 0
Viewed 18122

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;
    }
typeorm typescript node.js postgresql
2 Answers

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

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