Bulk update - lucid model


#1

I checked the docs but Im not understanding how to do this.

I have a form, where one user can have multiple pickups. The data is coming in something like below…

const Pickup = use(“App/Models/Pickup”);
const pickups = [ {id:1, city:downey,}, {id:2, city:paramount} ]

await Pickup.query().where(????????)
  .update(pickups);


  or am i supposed to loop through this ??

  for(let pickup of pickups){
    await Pickup.query()
      .where({ id: pickup.id })
      .update(pickup );

}


#2

As far as I’m aware you can’t do a bulk update of specific records, with different values like that. So you could do a loop, yeah.

If you’re using postgres (not sure if this works in mysql/sqlite) you could do something like this to get it done in a single query:

  const placeholders = []
  const values = []
  for (const pickup of pickups) {
    placeholders.push('(?,?)')
    values.push(pickup.id)
    values.push(pickup.city)
  }

  // Bulk update with individual pickup cities
  await Database.raw(`UPDATE pickups AS p SET city = v.city
                      FROM (VALUES ${placeholders.join(',')})
                      AS v(id, city) WHERE v.id = p.id`, values)

Not pretty, but it’ll be a single query instead of several, which would be good if you’re looking at more than just a few records having to be updated…

EDIT: this syntax does not work in mysql. :frowning: