Best practice for similar repeated queries using ActiveRecord

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.

2 answers

  • answered 2022-05-05 20:43 BenFenner

    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)
    

  • answered 2022-05-06 00:04 TonyArra

    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.

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum