Best practice for similar repeated queries using ActiveRecord

user_5911697 0
Viewed 55

I have a couple simple queries and I'm not sure what the best practice is.

I've written them in 2 ways which produce the same result. Which is preferred? Or is there a third, better way?

More concise:

Fruit.where(id: NOT_PREPACKAGED_RAW_FRUIT_IDS).update_all(prepackaged_raw_enabled: false)
Fruit.where.not(id: NOT_PREPACKAGED_RAW_FRUIT_IDS).update_all(prepackaged_raw_enabled: true)

More verbose:

fruits = Fruit.all
not_prepackaged = fruits.where(id: NOT_PREPACKAGED_RAW_FRUIT_IDS)
prepackaged = fruits - not_prepackaged
not_prepackaged.update_all(prepackaged_raw_enabled: false)
prepackaged.update_all(prepackaged_raw_enabled: true)

The purpose of the snippet is to do a onetime backfill.

activerecord ruby-on-rails ruby
2 Answers

If you want to do it in a single statement/query, you can write it like this:

Fruit.update_all(prepackaged_raw_enabled:
  Fruit.arel_table[:id].not_in(NOT_PREPACKAGED_RAW_FRUIT_IDS)
)

If NOT_PREPACKAGED_RAW_FRUIT_IDS = [571, 572], that would translate to the following SQL, which will update all of the records at once:

UPDATE "fruits" 
SET "prepackaged_raw_enabled" = "fruits"."id" NOT IN (571, 572)
user_812160

Without any more context, your first example is easier to follow. Assuming you want to touch every single record, it might improve clarity (although would be a little slower) if you backfill all records, then just the subset:

Fruit.update_all(prepackaged_raw_enabled: true)
Fruit.where(id: NOT_PREPACKAGED_RAW_FRUIT_IDS).update_all(prepackaged_raw_enabled: false)

Or maybe it's safer for your setup to do it the other way around (although the double negative is not ideal):

Fruit.update_all(prepackaged_raw_enabled: false)
Fruit.where.not(id: NOT_PREPACKAGED_RAW_FRUIT_IDS).update_all(prepackaged_raw_enabled: true)
user_148377820
Related