CODEWITHSUNDEEP
sequelize.js
engineerGuido
engineerGuido

Reputation: 21

Complex destroy query

I'm trying to come up with some code (other than a RAW sequelize query) that will allow me to delete all but N newest rows of a given model in one transaction/query.

I've tried the naive approach of using offset and order in the Model.detroy method.

Model.destroy({
  order: ["createdAt", "DESC"],
  offset: N,
  where: {
    parentModelId: someId,
  }
})

What I expected is that the code above constructed a query such as:

```sql
DELETE FROM model WHERE "parentModelId" = 'someId' ORDER BY "createdAt" DESC OFFSET N;

Instead, the query generated was:

DELETE FROM model WHERE "parentModelId" = 'someId';

Upvotes: 1

Views: 416

Answers (1)

engineerGuido
engineerGuido

Reputation: 21

Edit: Just realized the problem is not with Sequelize.js itself, but rather with PostgreSQL no allowing for such query in the first place. The appropiate way to achieve this is via the query:

DELETE FROM models WHERE is IN (
  SELECT id FROM models WHERE "parentModelId" = 'someId'
    ORDER BY "createdAt" DESC
    OFFSET N
);

The only way to do this in Sequelize.js is via a raw query, unfortuantely. http://docs.sequelizejs.com/manual/raw-queries.html

Upvotes: 1

Related Questions