How to insert custom value after validation in rails model

Question

This has been really difficult to find information on. The crux of it all is that I've got a Rails 3.2 app that accesses a MySQL database table with a column of type POINT. Without non-native code, rails doesn't know how to interpret this, which is fine because I only use it in internal DB queries.

The problem, however, is that it gets cast as an integer, and forced to null if blank. MySQL doesn't allow null for this field because there's an index on it, and integers are invalid, so this effectively means that I can't create new records through rails.

I've been searching for a way to change the value just before insertion into the db, but I'm just not up enough on my rails lit to pull it off. So far I've tried the following:

...
after_validation :set_geopoint_blank
def set_geopoint_blank
  raw_write_attribute(:geopoint, '') if geopoint.blank?
  #this results in NULL value in INSERT statement
end

---------------------------

#thing_controller.rb
...
def create
  @thing = Thing.new
  @thing.geopoint = 'GeomFromText("POINT(' + lat + ' ' + lng + ')")'
  @thing.save
  # This also results in NULL and an error
end

---------------------------

#thing_controller.rb
...
def create
  @thing = Thing.new
  @thing.geopoint = '1'
  @thing.save
  # This results in `1` being inserted, but fails because that's invalid spatial data.
end

To me, the ideal would be to be able to force rails to put the string 'GeomFromText(...)' into the insert statement that it creates, but I don't know how to do that.

Awaiting the thoughts and opinions of the all-knowing community....

Have you looked at http://stackoverflow.com/questions/12716797/rails-3-custom-raw-sql-insert-statement for raw inserts and http://stackoverflow.com/questions/4995428/storing-lat-lng-values-in-mysql-using-spatial-point-type for handling geospatials? — steve klein, Apr 18 '15 at 16:02

score 0 · Accepted Answer · answered Apr 20 '15 at 03:46

Ok, I ended up using the first link in steve klein's comment to just insert raw sql. Here's what my code looks like in the end:

def create
  # Create a Thing instance and assign it the POSTed values
  @thing = Thing.new
  @thing.assign_attributes(params[:thing], :as => :admin)

  # Check to see if all the passed values are valid
  if @thing.valid?
    # If so, start a DB transaction
    ActiveRecord::Base.transaction do
      # Insert the minimum data, plus the geopoint
      sql = 'INSERT INTO `things`
             (`thing_name`,`thing_location`,`geopoint`)
             values (
                "tmp_insert",
                "tmp_location",
                GeomFromText("POINT(' + params[:thing][:lat].to_f.to_s + ' ' + params[:thing][:lng].to_f.to_s + ')")
             )'
      id = ActiveRecord::Base.connection.insert(sql)

      # Then load in the newly-created Thing instance and update it's values with the passed values
      @real_thing = Thing.find(id)
      @real_thing.update_attributes(b, :as => :admin)
    end

    # Notify the user of success
    flash[:message] = { :header => 'Thing successfully created!' }
    redirect_to edit_admin_thing_path(@real_thing)
  else
    # If passed values not valid, alert and re-render form
    flash[:error] = { :header => 'Oops! You\'ve got some errors:', :body => @thing.errors.full_messages.join("</p><p>").html_safe }
    render 'admin/things/new'
  end 
end

Not beautiful, but it works.

How to insert custom value after validation in rails model

1 Answers1