CODEWITHSUNDEEP
rubyactiverecordinsertarelsequel
Seamus Abshere
Seamus Abshere

Reputation: 8526

How can I use SQL functions when inserting with ActiveRecord?

With Sequel, it was easy to apply functions before inserting a row:

the_geom_nosrid = Sequel.function :ST_GeomFromText, params[:the_geom]
the_geom_4326   = Sequel.function :ST_SetSRID,      the_geom_nosrid,  4326
the_geom_900913 = Sequel.function :ST_Transform,    the_geom_4326,    900913

DB[:drawings].returning(:id).insert the_geom: the_geom_900913

That would result in a query like

INSERT INTO drawings(the_geom) VALUES (ST_Transform(ST_SetSRID(ST_GeomFromText(...), 4326), 900913))

How do I apply SQL functions when I'm inserting with ActiveRecord? This doesn't work:

class Drawing < ActiveRecord::Base
  before_create do
    the_geom_nosrid = Arel::Nodes::NamedFunction.new('ST_GeomFromText', [the_geom])
    the_geom_4326 = Arel::Nodes::NamedFunction.new('ST_SetSRID', [the_geom_nosrid, 4326])
    the_geom_900913 = Arel::Nodes::NamedFunction.new('ST_Transform', [the_geom_4326, 900913])

    # doesn't work: `Type Error: can't cast Arel::Nodes::NamedFunction to `
    # self.the_geom = the_geom_900913

    # doesn't work: tries to insert the text "ST_Transform(ST_SetSR..." into a geometry column
    # self.the_geom = Arel.sql(the_geom_900913.to_sql)
  end
end

Upvotes: 2

Views: 493

Answers (1)

Dan Kohn
Dan Kohn

Reputation: 34337

Solved with the amazing scuttle.io:

Drawing.create(
  :the_geom => Arel::Nodes::NamedFunction.new(
    'ST_Transform', [
      Arel::Nodes::NamedFunction.new(
        'ST_SetSRID', [
          Arel::Nodes::NamedFunction.new('ST_GeomFromText', ['foobar']), 4326
        ]
      ), 900913
    ]
  )
)

Upvotes: 1

Related Questions