Code to update all fields and columns in table with specific value?

I just created a migration which changes my default values for string and text fields to empty string rather than nil.

I need to update all existing records where a string or text field in this table is nil. This is what I am trying but just curious if there is a better way, I don’t quite have it working and just seems like there might be a better way:

model I am updating is TravelCard

travel_card_columns_hash = TravelCard.columns_hash
TravelCard.all.each do |travel_card|
  travel_card_columns_hash.keys.each do |key|
    if travel_card_columns_hash[key].type.downcase == 'string' || travel_card_columns_hash[key].type.downcase == 'text'
      if !eval('travel_card.key')
        travel_card.key = ''
      end
    end
  end
end

David Kahn wrote in post #971487:

I just created a migration which changes my default values for string
and
text fields to empty string rather than nil.

Why?

I need to update all existing records where a string or text field in
this
table is nil.

Why?

This is what I am trying but just curious if there is a
better
way, I don't quite have it working and just seems like there might be a
better way:

# model I am updating is TravelCard
    travel_card_columns_hash = TravelCard.columns_hash
    TravelCard.all.each do |travel_card|
      travel_card_columns_hash.keys.each do |key|
        if travel_card_columns_hash[key].type.downcase == 'string' ||
travel_card_columns_hash[key].type.downcase == 'text'
          if !eval('travel_card.key')
            travel_card.key = ''
          end
        end
      end
    end

There probably is. But I confess to not seeing any point to doing this
in the first place. What are you trying to achieve?

Best,

I just created a migration which changes my default values for string and text fields to empty string rather than nil.

I need to update all existing records where a string or text field in this table is nil. This is what I am trying but just curious if there is a better way, I don't quite have it working and just seems like there might be a better way:

Do it in the migration itself... as sql... it's simpler, it's self contained, it's right there along side the code that changes the default...

%w[text_field1 text_field2].each do |field|
  TravelCard.update_all("#{field} = ''", "#{field} IS NULL")
end

The only reason not to do the above is if you can't make the change at the same time you alter the defaults.

But like Marnen said, I'd take a good look at why you think this change is necessary... There are some benefits to being able to distinguish between empty strings and null values...

-philip

I just created a migration which changes my default values for string and text fields to empty string rather than nil.

I need to update all existing records where a string or text field in this table is nil. This is what I am trying but just curious if there is a better way, I don’t quite have it working and just seems like there might be a better way:

Do it in the migration itself… as sql… it’s simpler, it’s self contained, it’s right there along side the code that changes the default…

%w[text_field1 text_field2].each do |field|

TravelCard.update_all("#{field} = ‘’", “#{field} IS NULL”)

end

The only reason not to do the above is if you can’t make the change at the same time you alter the defaults.

But like Marnen said, I’d take a good look at why you think this change is necessary… There are some benefits to being able to distinguish between empty strings and null values…

Ok, actually I would like your ideas. The issue started with a problem in a view where I am using the field value to determine what css class I use on an element. Things were fine until I got a nil value out of the db because of course field#downcase fails if nil. So the conclusion I came to is that I dont want to have to add disgusting stuff to the view like:

and rather have

So that is where I realized I could either write logic in the model (more work) or set a default db value which would seem less work. I am not in love with the idea of default db values, never really have been, but it seemed like the best place. Basically in this case nil or empty string mean the same to me.

Is there a better option?

Thinking aloud I guess I could add a helper method to handle the value generation… which now in retrospect does seem easier than this kind of disgusting db migration

I just created a migration which changes my default values for string and text fields to empty string rather than nil.

I need to update all existing records where a string or text field in this table is nil. This is what I am trying but just curious if there is a better way, I don’t quite have it working and just seems like there might be a better way:

Do it in the migration itself… as sql… it’s simpler, it’s self contained, it’s right there along side the code that changes the default…

%w[text_field1 text_field2].each do |field|

TravelCard.update_all("#{field} = ‘’", “#{field} IS NULL”)

end

The only reason not to do the above is if you can’t make the change at the same time you alter the defaults.

But like Marnen said, I’d take a good look at why you think this change is necessary… There are some benefits to being able to distinguish between empty strings and null values…

Ok, actually I would like your ideas. The issue started with a problem in a view where I am using the field value to determine what css class I use on an element. Things were fine until I got a nil value out of the db because of course field#downcase fails if nil. So the conclusion I came to is that I dont want to have to add disgusting stuff to the view like:

and rather have

So that is where I realized I could either write logic in the model (more work) or set a default db value which would seem less work. I am not in love with the idea of default db values, never really have been, but it seemed like the best place. Basically in this case nil or empty string mean the same to me.

Is there a better option?

Thinking aloud I guess I could add a helper method to handle the value generation… which now in retrospect does seem easier than this kind of disgusting db migration

Yep! Helper method for the win :slight_smile:

-philip

I just created a migration which changes my default values for string and text fields to empty string rather than nil.

I need to update all existing records where a string or text field in this table is nil. This is what I am trying but just curious if there is a better way, I don’t quite have it working and just seems like there might be a better way:

Do it in the migration itself… as sql… it’s simpler, it’s self contained, it’s right there along side the code that changes the default…

%w[text_field1 text_field2].each do |field|

TravelCard.update_all("#{field} = ‘’", “#{field} IS NULL”)

end

The only reason not to do the above is if you can’t make the change at the same time you alter the defaults.

But like Marnen said, I’d take a good look at why you think this change is necessary… There are some benefits to being able to distinguish between empty strings and null values…

Ok, actually I would like your ideas. The issue started with a problem in a view where I am using the field value to determine what css class I use on an element. Things were fine until I got a nil value out of the db because of course field#downcase fails if nil. So the conclusion I came to is that I dont want to have to add disgusting stuff to the view like:

and rather have

So that is where I realized I could either write logic in the model (more work) or set a default db value which would seem less work. I am not in love with the idea of default db values, never really have been, but it seemed like the best place. Basically in this case nil or empty string mean the same to me.

Is there a better option?

Thinking aloud I guess I could add a helper method to handle the value generation… which now in retrospect does seem easier than this kind of disgusting db migration

Yep! Helper method for the win :slight_smile:

Yeah, what was I thinking :slight_smile: Like a flashback to my ASP.NET/SqlServer days, must be PTSD.