Update many does not work with relation which has the same field

Discord Q&A strapi-questions
1

Hi,

I’m trying to do an updateMany with the query engine. I have over 10.000 items which I have to update at once, so doing 10.000+ simple updates by ID does not seems to be a good option for me.

Let’s say I have the following content types:

interface Event {
  id: Entity.ID
  status: 'open' | 'started' | 'finished'
}

// Event has many EventDevice
interface EventDevice {
  id: Entity.ID
  status: 'pending' | 'approved' | 'declined' | 'expired'
  validUntil: Date
}

I try to update the status and the validUntil fields of all the devices belonging to an event. The query I try to run:

await strapi.db.query('api::event.event-device').updateMany({
  where: {
    event: { id: eventId },
    status: { $in: [EventDeviceStatus.Pending, EventDeviceStatus.Accepted] }
  },
  data: {
    validUntil: new Date(),
    status: EventDeviceStatus.Expired,
  }
})

However, this query fails, since both the Event and the EventDevice has status fields. (I’ll include the full error in a comment)

Are there any solutions for this?

Thank you,
David

This topic has been created from a Discord post (1231164372030717952) to give it more visibility.
It will be on Read-Only mode here.
Join the conversation on Discord

2

The full error:

update event_devices left join event_devices_event_links as t1 on t0.id = t1.event_device_id left join events as t2 on t1.event_id = t2.id set status = ‘expired’, valid_until = ‘2024-04-20 08:44:01.663’ where (t2.id = 2 and status in (‘pending’, ‘accepted’)) - ER_NON_UNIQ_ERROR: Column ‘status’ in where clause is ambiguous
Error: ER_NON_UNIQ_ERROR: Column ‘status’ in where clause is ambiguous
at Sequence._packetToError (/opt/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
at Query.ErrorPacket (/opt/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
at Protocol._parsePacket (/opt/node_modules/mysql/lib/protocol/Protocol.js:291:23)
at Parser._parsePacket (/opt/node_modules/mysql/lib/protocol/Parser.js:433:10)
at Parser.write (/opt/node_modules/mysql/lib/protocol/Parser.js:43:10)
at Protocol.write (/opt/node_modules/mysql/lib/protocol/Protocol.js:38:16)
at Socket. (/opt/node_modules/mysql/lib/Connection.js:88:28)
at Socket. (/opt/node_modules/mysql/lib/Connection.js:526:10)
at Socket.emit (node:events:518:28)
at Socket.emit (node:domain:488:12)

at Protocol._enqueue (/opt/node_modules/mysql/lib/protocol/Protocol.js:144:48)
at Connection.query (/opt/node_modules/mysql/lib/Connection.js:198:25)
at /opt/node_modules/knex/lib/dialects/mysql/index.js:132:18
at new Promise ()
at Client_MySQL._query (/opt/node_modules/knex/lib/dialects/mysql/index.js:126:12)
at executeQuery (/opt/node_modules/knex/lib/execution/internal/query-executioner.js:37:17)
at Client_MySQL.query (/opt/node_modules/knex/lib/client.js:154:12)
at Runner.query (/opt/node_modules/knex/lib/execution/runner.js:141:36)
at ensureConnectionCallback (/opt/node_modules/knex/lib/execution/internal/ensure-connection-callback.js:13:17)
at Runner.ensureConnection (/opt/node_modules/knex/lib/execution/runner.js:318:20)

3

I think you’d better try with $and, $or combo

4

Same result with this:

await strapi.db.query('api::event.event-device').updateMany({
  where: {
    $and: [
      { event: { id: eventId } },
      { status: { $in: [EventDeviceStatus.Pending, EventDeviceStatus.Accepted] } }
    ]
  },
  data: {
    validUntil: new Date(),
    status: EventDeviceStatus.Expired,
  }
})
5

The problem it is not specified which table should be used for status in the where clause: where (t2.id = 2 and status in ('pending', 'accepted'))

6

{ $or:[{ status: … }, { status: …}]}

7

Instead of $in

8

But that just a bulk idea, idc updateMany never worked for me…

9

Same result with this:

await strapi.db.query('api::event.event-device').updateMany({
  where: {
    event: { id: eventId },
    $or: [ {status: EventDeviceStatus.Pending }, {status: EventDeviceStatus.Accepted } ],
  },
  data: {
    validUntil: new Date(),
    status: EventDeviceStatus.Expired,
  }
})

This is the where clause now: where (t2.id = 2 and ((status = 'pending') or (status = 'accepted')))

10

I appreciate your effort, thank you :slightly_smiling_face:

11

Btw is status is event prop?

12

I mean event.status

13

status should filter the event devices, so evend_devices.status

14

Or it’s a device prop

15

Wonder if it works if you just remove status part

16

You may make a bigger OR instead of AND OR combo

17

Heh, no, it does not :face_holding_back_tears:

update `event_devices` left join `event_devices_event_links` as `t1` on `t0`.`id` = `t1`.`event_device_id` left join `events` as `t2` on `t1`.`event_id` = `t2`.`id` set `status` = 'expired', `valid_until` = '2024-04-20 11:01:40.908' where (`t2`.`id` = 2) - ER_BAD_FIELD_ERROR: Unknown column 't0.id' in 'on clause'\nError: ER_BAD_FIELD_ERROR: Unknown column 't0.id' in 'on clause
18

Like:
$or: [{ event: { id }, status}, {event

19

Yea that’s likely a problem…

20

You can prolly strap.db.connection