Query using Custom Attribute Types

rubyonrails-talk
,
1

Background

I have a legacy MySQL database where the schema is using “datetime” fields that are NOT NULL but then using the special invalid date of 0000-00-00 00:00:00 to indicate no value (sort of a pseudo-null value).

Rails will coerce this to nil when loaded into a model (which is great!). Furthermore I can query for the records I want by using this special value:

TimeApproval.where "approved_at = '0000-00-00 00:00:00''"
TimeApproval.where.not "approved_at = '0000-00-00 00:00'"

A bit ugly but it will work and sometimes you just have to deal with ugly when dealing with legacy schemas. :frowning:

Where I get a problem is if I need to create a record with this special value. Since it’s in invalid date there is no way to represent it as a Date object. But I also can’t just use nil since that will try to set the value to NULL and these columns are NOT NULL.

The only previous relevant discussion I found on this topic was this one. I think though I have a pretty decent solution using custom attribute types. Instead of assuming the column is a ActiveRecord::Type::DateTime type (based on the column type), I’m going to call it a “zero_date_time”. I’ll define my type as:

# Allows legacy MySQL databases that are using the legacy `sql_mode` of
# `ALLOW_INVALID_DATES` to work in Rails when a column is `NOT NULL`.
class ZeroDateTimeType < ActiveRecord::Type::DateTime
  def type = :zero_date_time

  PSEUDO_NULL = '0000-00-00 00:00:00'
  private_constant :PSEUDO_NULL

  def serialize value
    return PSEUDO_NULL if value.nil?

    super
  end
end

Register that with ActiveRecord with:

ActiveRecord::Type.register :zero_date_time, ZeroDateTimeType

Then finally use this custom attribute in my model:

class TimeApproval < ApplicationRecord
  ....
  attribute :approved_at, :zero_date_time
end

Low and behold it works! I now can do:

TimeApproval.create! approved_at: nil, ....

And it creates a record but stores the value of 0000-00-00 00:00:00! :tada:

Actual Question

Reading the docs on the custom types I think I should be able to also query with nil and it serialize to 0000-00-00 00:00:00 allowing my “where” clauses to be much cleaner. So I give it a try and no dice:

> puts TimeApproval.where(approved_at: nil).to_sql
SELECT `time_approvals`.* FROM `time_approvals` WHERE `time_approvals`.`approved_at` IS NULL

In fact, when I drop a debugger statement in my custom type it never even enters the serialize method. :frowning: Are my hopes to abstract this 0000-00-00 00:00:00 nonsense dashed?

When trying to understand why, I found if I put another value it did go into my custom type. For example:

TimeApproval.where approved_at: ''

In fact, if I changed my custom type to have:

return PSEUDO_NULL if value.blank?

And used '' it gave the exact behavior I want. Therefore my best guess was that Rails was seeing the nil and just assuming NOT NULL without actually using the custom type. To verify this, I tracked down where the NOT NULL is being added. In that code, right is an instance of ActiveRecord::Relation::QueryAttribute. It’s nil? method has a few conditions but the key one is:

value_before_type_cast.nil?

The value_before_type_cast is the nil passed into the where clause. So this explains why it bypasses my custom type. But is that the right behavior? Shouldn’t it determine nil? based on the value_for_database? Or am I missing something?

In the end, just being able to create records with 0000-00-00 00:00:00 is good enough. I can live with odd querying. But it would be ideal if I could pass nil to my where clause and it auto-translated that to 0000-00-00 00:00:00 just like it will do for any other value. This would allow me to code as if my DB is normal and then later when I refactor that DB to remove the NOT NULL and convert all values that are 0000-00-00 00:00:00 to a true NULL then I can just remove the usage of my custom type and everything else will remain the same.

2

It’s a little weird, but have you tried using normalizes in addition to (or instead of) your custom attribute? There’s a chance (I didn’t verify) that it processes nils correctly in all cases. I actually went the custom attribute type route in a few places, and ended up abandoning them for simple normalizes calls.

3

I appreciate the brain-storming, but I think normalizes operates at a higher level than I need. At the model level I want it to be either a Time or nil. But at the DB level, it should write 0000-00-00 00:00:00 if nil due to the field being NOT NULL in the database.

That value is a special invalid date that MySQL allowed on legacy versions (that you can still enable on current versions). The right schema of course would be to change the column to allow NULL and update all the places where that special value is to be NULL. But for legacy reasons I can’t yet so needed a way to be able to create records that are compat with this legacy schema.

My custom type does that. But it’s also tantalizing close to doing that when querying which would be awesome! But it doesn’t quite work because nil seems to not go through the custom types. I can’t tell if I’m doing something wrong or if Rails is wrong here.

4

But what I’m thinking, is that if you add:

normalizes :approved_at,
  with: -> value { value.nil? ? '0000-00-00 00:00:00' : value }

in addition to your type, then it will cover that last where use case.

P.S. And yes, I feel your pain that type doesn’t pass-through the nil, I would think it’s a Rails bug based on what you are explaining. Nevertheless, could this be a workaround?

1 Like