CODEWITHSUNDEEP
mysqlruby-on-railsactiverecord
kolrie
kolrie

Reputation: 12732

Inserting an invalid ActiveRecord date to a MySQL database

MySQL allows the value of 0000-00-00 for date fields, however ActiveRecord treats an assignment to a Date value with that string as being nil.

I have a legacy application that relies on that exact field content for the notion of "Never", so if the customer last payment date is 0000-00-00 it acknowledges as being "Never" paid.

Is there a way I can override ActiveRecord (correct) validation for this date field and write this value to the database without getting my hands (too) dirty?

Thanks for any insight.

Upvotes: 3

Views: 795

Answers (4)

Dan Lynn
Dan Lynn

Reputation: 1

I ran into this on a Rails 3.0.3 project and monkey patched the following which allows you to assign '0000-00-00' to a model date field. This value will be carried through to the mysql database column on insert/update.

  class ActiveRecord::ConnectionAdapters::Column
    def self.string_to_date_with_zero_support(string)
      return string if string == '0000-00-00'
      string_to_date_without_zero_support(string)
    end

    class << self
      alias_method_chain(:string_to_date, :zero_support)
    end
  end

Upvotes: 4

Omar Qureshi
Omar Qureshi

Reputation: 9093

You could change the default value on the database to be "0000-00-00" using a proper sql alter table alter column ...; statement.

Upvotes: 0

Steve Weet
Steve Weet

Reputation: 28392

You may find this slightly easier and it should achieve the same result without executing the method for every attribute in the model

class CustomerInfo < BillingDatabase
  belongs_to :customer

  def lastpaid
    value = read_attribute(:lastpaid)
    value.blank? ? "0000-00-00" : value
  end
end

You could of course refactor the code to call read_attribute twice and do the lastpaid method as a one-liner. It's a matter of style/minute performance differences really.

def lastpaid
  read_attribute(:lastpaid).blank? "0000-00-00" : read_attribute(:lastpaid)
end

Upvotes: 2

kolrie
kolrie

Reputation: 12732

I hate answering my own questions, but after a little more research about AR code itself, I came up with this solution:

class CustomerInfo < BillingDatabase
  belongs_to :customer

  alias_method :old_read_attribute, :read_attribute

  def read_attribute(attr_name)
    if attr_name == "lastpaid" and old_read_attribute(attr_name).blank?
      "0000-00-00"
    else
      old_read_attribute(attr_name)
    end
  end
end

I will leave this question open in case someone has a better approach or want to comment on possible flaws.

Upvotes: 1

Related Questions