Best practice for similar repeated queries using ActiveRecord-CodePudding

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.

CodePudding user response：

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)

CodePudding user response：

The "best" way is subjective. But 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)

It's not necessarily the most readable solution, but it is the most efficient.