0

I have two tables in a 1-many relationship, Device and TestResult. A Device can have many TestResults.

My application presents user with a filterable search page that returns a list of TestResults. It's effectively a query builder in the back end. The user can add filters based on both Device attributes and TestResult attributes. For instance, a user may search for all TestResults that passed and were performed during a time range, for Devices who's serial number falls within a specific range. The results should be grouped by Device, so querying from the Device class.

Here is an example of what a FindOptions object might look like, which I would pass to the Device.findAndCountAll() method:

let results = Device.findAndCountAll(
  {
    include: [
      {
        model: TestResult,
        attributes: [
          'id',
          'testBlockId',
          'deviceId',
          'type',
          'fieldResponses',
          'stationName',
          'summary',
          'createdAt'
        ],
        where: {
          createdAt: { [Symbol(gte)]: '2020-03-27T11:54:43.100Z' },
          stationName: { [Symbol(in)]: [ 'Red', 'Green' ] },
          fieldResponses: {
            [Symbol(and)]: [
              { [Symbol(like)]: '%"Customer":"CustomerA"%' },
              { [Symbol(like)]: '%"Batch":"4"%' }
            ]
          },
          testBlockId: { [Symbol(in)]: [ 2, 3 ] },
          summary: 'True'
        },
        as: 'testResults'
      }
    ],
    attributes: [ 'id', 'serialNumber', 'createdAt' ],
    limit: 100,
    offset: 0,
    order: [ [ 'serialNumber', 'ASC' ] ],
    where: {
      serialNumber: { [Symbol(between)]: [ '000000001000', '000000200000' ] }
    }
  }
)

I'm now trying to add an option to delete all TestResult records that are returned by one of these searches but I'm not sure what the proper way to do this with Sequelize is.

The DestroyOptions type does not have an include[] attribute, so I don't know how to add an INNER JOIN to a DELETE query in Sequelize.

It might be possible to call TestResults.findAll() and destroy records in the .then() function, but I haven't figured out how to do this. Without a LIMIT set, the query might return hundreds of thousands of rows, way too many to hold in memory after Sequelize turns them all into complex objects. I also don't want to delete the records one at a time.

Ideally, a query would look like this:

DELETE FROM testResult
WHERE summary = 'True'
AND deviceId IN (
  SELECT id FROM device
  WHERE serialNumber BETWEEN '0000000010000' AND '000000002000'
);

But I don't know how to achieve a subquery like that in Sequelize.

Is there a proper way, using Sequelize, to do bulk deletes with complex WHERE clauses?

Chris.B
  • 493
  • 1
  • 5
  • 16
  • I'm not sure i understand. Tell me if i'm right: You want to delete all TestResults , that are associated with a certain Device? If so, that means you have some device id, and that TestResults table contains a column deviceId. Right? – i.brod Apr 01 '20 at 15:18
  • Yes, that's basically it. I just added an edit before seeing your question. There could be thousands of deviceIds that match the filters, so the deviceIds should be returned by a subquery. I don't know how to achieve that with Sequelize. – Chris.B Apr 01 '20 at 15:21
  • 1
    I never did something like that with sequelize, and to be honest i think this is one of those cases where peforming a raw SQL is much easier than digging in the poorly written docs of Sequelize, in order to figure out how to do it "their way". I would do it with a raw query...But if you want to explore the Sequelize way further, maybe take a look here: https://stackoverflow.com/questions/28286811/sequelize-subquery-as-field – i.brod Apr 01 '20 at 15:29
  • Thanks, that's what I was thinking as well. The reason I didn't make the query builder using raw queries to begin with was that it was a pain to take the raw returned data and put it back into Sequelize model objects. But the question you linked shows that Sequelize will do that for me if I just specify `{raw: false}`. I missed that in the Sequelize docs before. – Chris.B Apr 01 '20 at 15:40

0 Answers0